Skip site navigation (1) Skip section navigation (2)

plpgsql variable substitution problem ...

From: Greg Wickham <greg(at)geelong(dot)com>
To: pgsql-sql(at)hub(dot)org
Subject: plpgsql variable substitution problem ...
Date: 2000-05-28 17:48:11
Message-ID: 20000528174811.70CF510B72@mail.surfcoast.net (view raw or flat)
Thread:
Lists: pgsql-sql
[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(at)geelong(dot)com
Geelong VIC 3220                             Ph: (+61 407) 854 566
------------------------------------------------------------------
thEsepRetzelsareMakingmetHirstythEsepRetzelsareMakingmetHirstythEs

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2000-05-28 18:15:44
Subject: Re: plpgsql variable substitution problem ...
Previous:From: Andreas ZeugswetterDate: 2000-05-28 07:34:40
Subject: Re: Re: [SQL] aliases break my query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group