How create SQL variable sentense in plpgsql?!

From: "Mikhail V(dot) Majorov" <mik(at)ttn(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: How create SQL variable sentense in plpgsql?!
Date: 2001-02-13 17:45:07
Message-ID: 3A8972A3.8C48209D@ttn.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all!

I try to make multi-purpose trigger for make history off change in some
tables.
The main purpose is to do "INSERT INTO textcat(TG_NAME, '_history')
VALUES (OLD)".
Is it possible? I have all time error: ERROR: parser: parse error at or
near "$1"

Below I type example of SQL command for this.

CREATE TABLE my_table1 (...);
CREATE TABLE my_table1_history () INHERITS (my_table1);

CREATE TABLE my_table2 (...);
CREATE TABLE my_table2_history () INHERITS (my_table2);

CREATE TABLE my_table3 (...);
CREATE TABLE my_table3_history () INHERITS (my_table3);

CREATE FUNCTION make_history() RETURNS opaque AS '
DECLARE
dbname text;
BEGIN
dbname := textcat(TG_NAME, ''_history'');
INSERT INTO dbname VALUES (OLD);
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ I have problem in this line :(
END;
' as LANGUAGE 'plpgsql';

CREATE TRIGGER history_of_my_table1 AFTER UPDATE OR DELETE
ON my_table1 FOR EACH ROW
EXECUTE PROCEDURE make_history();

CREATE TRIGGER history_of_my_table2 AFTER UPDATE OR DELETE
ON my_table2 FOR EACH ROW
EXECUTE PROCEDURE make_history();

CREATE TRIGGER history_of_my_table3 AFTER UPDATE OR DELETE
ON my_table3 FOR EACH ROW
EXECUTE PROCEDURE make_history();

Best regards,
Mikhail.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Anand Raman 2001-02-13 17:51:38 Re: [araman@india-today.com: locking problem with JDBC (suspicion)]
Previous Message Mikhail V. Majorov 2001-02-13 17:37:20 How create SQL variable sentense in plpgsql?!