"function has no parameter $1"

From mweaver@corpusglobe.com Fri Feb 7 08:02:30 2003 X-Original-To: pgsql-novice@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 116CC475956; Fri, 7 Feb 2003 03:02:30 -0500 (EST) Received: from vision1.touristvision.com.au (vision1.corpusglobe.com [202.61.128.2]) by postgresql.org (Postfix) with ESMTP id 3CCEC475E22; Fri, 7 Feb 2003 02:55:53 -0500 (EST) Received: by VISION1 with Internet Mail Service (5.5.2653.19) id ; Fri, 7 Feb 2003 16:10:36 +0800 Message-ID: <3B663B41B350D311AEEF00A0C9254563201A69@VISION1> From: Michael Weaver To: "Pgsql-Novice (pgsql-novice@postgresql.org)" , "'Pgsql-SQL (pgsql-sql@postgresql.org)'" Subject: "function has no parameter $1" - help. Date: Fri, 7 Feb 2003 16:10:29 +0800 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2CE80.608A7E60" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/122 X-Sequence-Number: 6138 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C2CE80.608A7E60 Content-Type: text/plain; charset="iso-8859-1" I'm trying to create a trigger to enforce a constraint onto two possible foreign tables my function is defined as: <-- snip --> CREATE OR REPLACE FUNCTION sp_check_tranitem_productid(tbl_tranitem.fld_tranitem_id%TYPE, tbl_tranitem.fld_tranitem_type%TYPE) RETURNS OPAQUE AS ' DECLARE -------[Parameters]------- pl_product_id ALIAS FOR $1; pl_product_type ALIAS FOR $2; <-- snip --> but when I call the function I get the error: function has no parameter $1 What is going wrong? (I am using PostgreSQL 7.3.1) , Mike Weaver Software Developer 5, 42 Ladner Street O'Connor, WA, 6163 All correspondence: PO Box Y3502 East St Georges Terrace Perth WA 6832 P: (+618) 9331 2700 F: (+618) 9331 3733 M: 0403 385 181 W: http://www.corpusglobe.com/ E: mweaver@corpusglobe.com This email is intended only for the use of the individual or entity named above and may contain information that is confidential. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. When addressed to our clients, any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing Corpus Globe client engagement letter. If you have received this Email in error, please notify us immediately by return email or telephone +61 8 9331 2700 and destroy the original message. Thank You. ------_=_NextPart_001_01C2CE80.608A7E60 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable "function has no parameter $1" - help.

I'm trying to create a trigger to enforce a constraint on= to two possible foreign tables
my function is defined as:

<-- snip -->
CREATE OR REPLACE FUNCTION sp_check_tranitem_productid(t= bl_tranitem.fld_tranitem_id%TYPE,
            &nbs= p;              = ;     tbl_tranitem.fld_tranitem_type%TYP= E) RETURNS OPAQUE AS '
DECLARE
-------[Parameters]-------
        pl_product_id=    ALIAS FOR $1;
        pl_product_ty= pe ALIAS FOR $2;
<-- snip -->

but when I call the function I get the error:

function has n= o parameter $1

What is going wrong?

(I am using PostgreSQL 7.3.1)
,

Mike Weaver
Software Developer

5, 42 Ladner Street
O'Connor, WA, 6163
All correspondence:
PO Box Y3502
East St Georges Terrace
Perth WA 6832

P: (+618) 9331 2700
F: (+618) 9331 3733
M: 0403 385 181
W: http://www.corpusglobe.com/
E: mweaver@corpusglobe.com

This email is intended only for the use of the individual= or entity named above and may contain information that is confidential. If= you are not the intended recipient, you are hereby notified that any disse= mination, distribution or copying of this email is strictly prohibited. Whe= n addressed to our clients, any opinions or advice contained in this email = are subject to the terms and conditions expressed in the governing Corpus G= lobe client engagement letter. If you have received this Email in error, pl= ease notify us immediately by return email or telephone +61 8 9331 2700 and= destroy the original message. Thank You.

------_=_NextPart_001_01C2CE80.608A7E60-- From ch@rodos.fzk.de Fri Feb 7 08:28:52 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3E1FB47592C for ; Fri, 7 Feb 2003 03:28:52 -0500 (EST) Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by postgresql.org (Postfix) with SMTP id 04F824758FE for ; Fri, 7 Feb 2003 03:28:51 -0500 (EST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Fri Feb 07 09:28:50 2003 +0100 Received: śby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id JAA15425; Fri, 7 Feb 2003 09:28:50 +0100 (MET) Message-ID: <3E436E41.ABA852F5@rodos.fzk.de> Date: Fri, 07 Feb 2003 09:28:50 +0100 From: Christoph Haller X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Cc: mweaver@corpusglobe.com Subject: Re: "function has no parameter $1" - help. Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/141 X-Sequence-Number: 11539 > > I'm trying to create a trigger to enforce a constraint onto two possible > foreign tables > my function is defined as: > > <-- snip --> > CREATE OR REPLACE FUNCTION > sp_check_tranitem_productid(tbl_tranitem.fld_tranitem_id%TYPE, > tbl_tranitem.fld_tranitem_type%TYPE) RETURNS > OPAQUE AS ' > DECLARE > -------[Parameters]------- > pl_product_id ALIAS FOR $1; > pl_product_type ALIAS FOR $2; > <-- snip --> > > but when I call the function I get the error: > > function has no parameter $1 > > What is going wrong? > > (I am using PostgreSQL 7.3.1) > The documentation says about trigger procedures: PL/pgSQL can be used to define trigger procedures. A trigger procedure is created with the CREATE FUNCTION command as a function with no arguments and a return type of OPAQUE. Note that the function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER --- trigger arguments are passed via TG_ARGV, as described below. Refer to that chapter for an example. Regards, Christoph From daniel_schuchardt@web.de Fri Feb 7 17:32:37 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 98217474E4F for ; Fri, 7 Feb 2003 12:32:37 -0500 (EST) Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id 2BEC0475956 for ; Fri, 7 Feb 2003 12:32:27 -0500 (EST) Received: by news.hub.org (Postfix, from userid 8) id 0333043A67A; Fri, 7 Feb 2003 12:31:51 -0500 (EST) From: "Daniel Schuchardt" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: "function has no parameter $1" - help. Date: Fri, 7 Feb 2003 17:52:34 +0100 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 7 Message-ID: References: <3B663B41B350D311AEEF00A0C9254563201A69@VISION1> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 To: pgsql-sql@postgresql.org X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/161 X-Sequence-Number: 11559 In > 7.3 you can also CREATE OR REPLACE FUNCTION xxx() RETURNS TRIGGER AS' BEGIN END'LANGUAGE plpgsql;