From markjcamilleri@yahoo.co.uk Thu Jul 14 07:45:58 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 22B9952D59 for ; Thu, 14 Jul 2005 04:45:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09509-09 for ; Thu, 14 Jul 2005 07:45:56 +0000 (GMT) Received: from smtp002.mail.ukl.yahoo.com (smtp002.mail.ukl.yahoo.com [217.12.11.33]) by svr1.postgresql.org (Postfix) with SMTP id 4E4EC52D55 for ; Thu, 14 Jul 2005 04:45:54 -0300 (ADT) Received: (qmail 28803 invoked from network); 14 Jul 2005 07:45:55 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk; h=Received:From:To:Subject:Date:MIME-Version:Content-Type:X-Mailer:x-mimeole:Thread-Index; b=A8wKOxnZ0ctv4h8sx8TpwokwFJ4uIMZCpBFI8gSAqYRoJS7P92OsiEhPBMkQCjcwhlcV3uHlFa/JaFkhsB8jFaYDePKaK4CciXigrocY+dEv/PSTLeSCc6oCeXdgV05UDSrFnBHyBUcRYpzoBy+ZyxOnZELMoTcp3dZRCil/Dp8= ; Received: from unknown (HELO yourdmnnkz5cls) (markjcamilleri@195.158.96.19 with login) by smtp002.mail.ukl.yahoo.com with SMTP; 14 Jul 2005 07:45:54 -0000 From: "Mark J Camilleri" To: Subject: Error on dynamic code. Date: Thu, 14 Jul 2005 09:46:59 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0010_01C58858.FB7C3CD0" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcWISDcF2JD0y+zbSuOb3EUJikkMAQ== Message-Id: <20050714074554.4E4EC52D55@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.079 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_MESSAGE, MSGID_FROM_MTA_ID X-Spam-Level: ** X-Archive-Number: 200507/131 X-Sequence-Number: 22109 This is a multi-part message in MIME format. ------=_NextPart_000_0010_01C58858.FB7C3CD0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0010_01C58858.FB7C3CD0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 = :=3D '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=3D3;

--...more =
code

END;

(full code can be found at http://www.postgresql.org/docs/8.0/i= nteractive/plpgsql-statements.html#PLPGSQL-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

------=_NextPart_000_0010_01C58858.FB7C3CD0-- ___________________________________________________________ How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com From desoi@pgedit.com Fri Jul 15 19:26:44 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BA58552C5D for ; Fri, 15 Jul 2005 16:26:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20494-09 for ; Fri, 15 Jul 2005 19:26:40 +0000 (GMT) Received: from a2s1.a2webhosting.com (host-180.a2webhosting.com [69.39.89.180]) by svr1.postgresql.org (Postfix) with ESMTP id 6B98B52C16 for ; Fri, 15 Jul 2005 16:26:39 -0300 (ADT) Received: from user-11204q7.dsl.mindspring.com ([66.32.19.71] helo=[192.168.1.10]) by a2s1.a2webhosting.com with esmtpsa (TLSv1:RC4-SHA:128) (Exim 4.50) id 1DtVpY-0001PL-5c; Fri, 15 Jul 2005 15:26:40 -0400 In-Reply-To: <20050714074554.4E4EC52D55@svr1.postgresql.org> References: <20050714074554.4E4EC52D55@svr1.postgresql.org> Mime-Version: 1.0 (Apple Message framework v730) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <5C174834-4EDD-46C2-9C73-A02B3D148545@pgedit.com> Cc: Content-Transfer-Encoding: 7bit From: John DeSoi Subject: Re: Error on dynamic code. Date: Fri, 15 Jul 2005 15:26:35 -0400 To: Mark J Camilleri X-Mailer: Apple Mail (2.730) X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - a2s1.a2webhosting.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - pgedit.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.013 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/147 X-Sequence-Number: 22125 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