From: Greg Wickham <greg@geelong.com>
To: pgsql-sql@hub.org
Subject: plpgsql variable substitution problem ...
Date: Mon, 29 May 2000 03:48:11 +1000
Message-ID: <20000528174811.70CF510B72@mail.surfcoast.net> (raw)
[Hopefully the correct list this time :)]
Howdy. Creating a function to access different tables depending
on the arguments. However, am having excessive problems.
Current code is as follows:
CREATE FUNCTION access(varchar,varchar) RETURNS INT4 AS '
DECLARE
lcl_field ALIAS FOR $1;
lcl_table ALIAS FOR $2;
lcl_max INT;
BEGIN
SELECT max(lcl_field) INTO lcl_max FROM lcl_table;
RETURN lcl_max;
END;
' LANGUAGE 'plpgsql';
However, if I execute the following code
SELECT getsequence('f1','t1');
I get an error:
psql:sequence.db:35: ERROR: parser: parse error at or near "$2"
(What I really want is to execute a "SELECT max(f1) FROM f2")
The output from the postmaster is:
000528.19:45:45.682 [9553] CommitTransactionCommand
000528.19:45:45.713 [9553] StartTransactionCommand
000528.19:45:45.713 [9553] query: SELECT access('f1','t1'));
000528.19:45:45.717 [9553] ProcessQuery
000528.19:45:45.725 [9553] query: SELECT max( $1 ) FROM $2
000528.19:45:45.726 [9553] ERROR: parser: parse error at or near "$2"
000528.19:45:45.726 [9553] DEBUG: Last error occured while executing PL/pgSQL function getsequence
000528.19:45:45.726 [9553] DEBUG: line 6 at select into variables
000528.19:45:45.726 [9553] AbortCurrentTransaction
Any ideas? I've fiddled with the variables and procedures a few
times but I can't quite get a breakthrough.
tia,
-Greg
------------------------------------------------------------------
www.geelong.com Greg Wickham
P.O. Box 1426 greg@geelong.com
Geelong VIC 3220 Ph: (+61 407) 854 566
------------------------------------------------------------------
thEsepRetzelsareMakingmetHirstythEsepRetzelsareMakingmetHirstythEs
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: greg@geelong.com, pgsql-sql@hub.org
Subject: Re: plpgsql variable substitution problem ...
In-Reply-To: <20000528174811.70CF510B72@mail.surfcoast.net>
References: <20000528174811.70CF510B72@mail.surfcoast.net>
* 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