Received: from mail.surfcoast.net (IDENT:postfix@gateway.geelong.com [203.23.178.223]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA21452 for ; Sun, 28 May 2000 13:48:31 -0400 (EDT) (envelope-from greg@surfcoast.net) Received: from vhosts.surfcoast.net (localhost [127.0.0.1]) by mail.surfcoast.net (Postfix) with ESMTP id 70CF510B72 for ; Mon, 29 May 2000 03:48:11 +1000 (EST) To: pgsql-sql@hub.org From: Greg Wickham Subject: plpgsql variable substitution problem ... X-Http-Homepage: http://www.geelong.com Date: Mon, 29 May 2000 03:48:11 +1000 Message-Id: <20000528174811.70CF510B72@mail.surfcoast.net> X-Archive-Number: 200005/176 [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