Thread: How create SQL variable sentense in plpgsql?!

How create SQL variable sentense in plpgsql?!

From
"Mikhail V. Majorov"
Date:
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.

Re: How create SQL variable sentense in plpgsql?!

From
Peter Eisentraut
Date:
Mikhail V. Majorov writes:

> 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"

Upgrade to 7.1 and use the EXECUTE command, or write it in a different
programming language.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/