pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
plpgsql variable substitution problem ...
2+ messages / 2 participants
[nested] [flat]

* plpgsql variable substitution problem ...
@ 2000-05-28 17:48  Greg Wickham <greg@geelong.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Greg Wickham @ 2000-05-28 17:48 UTC (permalink / raw)
  To: pgsql-sql@hub.org


[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



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

* Re: plpgsql variable substitution problem ...
@ 2000-05-28 18:15  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Greg Wickham <greg@geelong.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2000-05-28 18:15 UTC (permalink / raw)
  To: Greg Wickham <greg@geelong.com>; +Cc: pgsql-sql@hub.org

Greg Wickham <greg@geelong.com> writes:
> 000528.19:45:45.725  [9553] query: SELECT  max( $1 ) FROM  $2

There is no facility in plpgsql for run-time specification of a table
name, and probably never will be for the general case.  It's possible
that it could be made to work for selection of a particular table among
the inheritance children of a specified table, but not for any arbitrary
table, unless we want to give up preparsing and preplanning of plpgsql
queries.

pltcl does everything on-the-fly at runtime, so you could do this in
that language, I think (and accept the resulting speed penalty).

A more interesting question is whether you shouldn't redesign your
database schema so that you don't need to do this...

			regards, tom lane



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


end of thread, other threads:[~2000-05-28 18:15 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-28 17:48 plpgsql variable substitution problem ... Greg Wickham <greg@geelong.com>
2000-05-28 18:15 ` Tom Lane <tgl@sss.pgh.pa.us>

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