From: Mark J Camilleri <markjcamilleri@yahoo.co.uk>
To: pgsql-sql@postgresql.org
Subject: Error on dynamic code.
Date: Thu, 14 Jul 2005 09:46:59 +0200
Message-ID: <20050714074554.4E4EC52D55@svr1.postgresql.org> (raw)
I am trying to write a dynamic trigger function on insert operations so that
the same function can be used across all my tables. In this case my tables
are 'test' and 'test_a' (my convention is that all audit table names are the
name of the original table concatenated with '_a'). Below is part of my
code in plpgsql:
CREATE OR REPLACE FUNCTION audit_insert()
RETURNS "trigger" AS
$BODY$
DECLARE
new_audit_row RECORD;
dynamic_SQL text;
BEGIN
--Instantiate new_audit_row to the required type.
dynamic_SQL := 'SELECT INTO new_audit_row * ' ||
'FROM ' || quote_ident(TG_RELNAME || '_a') || ';';
EXECUTE dynamic_SQL;
--... more code here
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
However, when I try to insert data in 'test' I am getting the following
error message:
ERROR: syntax error at or near "INTO" at character 8
QUERY: SELECT INTO new_audit_row * FROM "test_a";
CONTEXT: PL/pgSQL function "audit_insert" line 18 at execute statement
The funny thing is that the documentation I read about SELECT INTO and
RECORD types give the following example, amongst others:
DECLARE
users_rec RECORD;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;
--...more code
END;
(full code can be found at
http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGS
QL-STATEMENTS-ASSIGNMENT )
Which is basically identical (except for the WHERE clause) to the query
returned in the my error message!!
Can anyone help please?
Regs,
Mark J Camilleri
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: markjcamilleri@yahoo.co.uk
Subject: Re: Error on dynamic code.
In-Reply-To: <20050714074554.4E4EC52D55@svr1.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox