pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Error on dynamic code.
2+ messages / 2 participants
[nested] [flat]

* Error on dynamic code.
@ 2005-07-14 07:46  Mark J Camilleri <markjcamilleri@yahoo.co.uk>
  0 siblings, 1 reply; 2+ messages in thread

From: Mark J Camilleri @ 2005-07-14 07:46 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Error on dynamic code.
@ 2005-07-15 19:26  John DeSoi <desoi@pgedit.com>
  parent: Mark J Camilleri <markjcamilleri@yahoo.co.uk>
  0 siblings, 0 replies; 2+ messages in thread

From: John DeSoi @ 2005-07-15 19:26 UTC (permalink / raw)
  To: Mark J Camilleri <markjcamilleri@yahoo.co.uk>; +Cc: pgsql-sql


On Jul 14, 2005, at 3:46 AM, Mark J Camilleri wrote:


>
>
> The funny thing is that the documentation I read about SELECT INTO  
> and RECORD types give the following example, amongst others:
See the section below that on EXECUTE:

> The results from SELECT commands are discarded by EXECUTE, and  
> SELECT INTO is not currently supported within EXECUTE. So there is  
> no way to extract a result from a dynamically-created SELECT using  
> the plain EXECUTE command. There are two other ways to do it,  
> however: one is to use the FOR-IN-EXECUTE loop form described in  
> Section 35.7.4, and the other is to use a cursor with OPEN-FOR- 
> EXECUTE, as described in Section 35.8.2.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2005-07-15 19:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-14 07:46 Error on dynamic code. Mark J Camilleri <markjcamilleri@yahoo.co.uk>
2005-07-15 19:26 ` John DeSoi <desoi@pgedit.com>

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