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

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search archives
  Advanced Search

question - plpgsql and query on table given by variable


  • From: " Jaromír Kamler" <kamler(at)centrum(dot)cz>
  • To: <pgsql-novice(at)postgresql(dot)org>
  • Subject: question - plpgsql and query on table given by variable
  • Date: Sun, 28 Aug 2005 23:43:00 +0200
  • Message-id: <200508282343.16425@centrum.cz> <text/plain>

Please, help me.
I am new with PostgreSQL, I need create function in plpgsql where I use SQL question on the name of table whitch is given by variable in called function. Here is absolutly simple example:

CREATE OR REPLACE FUNCTION pokus(varchar) RETURNS int4 AS '
DECLARE
  v ALIAS FOR $1;
BEGIN
  SELECT AsText(the_geom) FROM v;
  RETURN 0;
END;
' LANGUAGE plpgsql; 

And this is error:

SELECT pokus('sometable');
ERROR: syntax error at or near "$1" at character 32
QUERY: SELECT Astext(the_geom) FROM $1
CONTEXT: PL/pgSQL function "pokus" line 4 at SQL statement
LINE 1: SELECT Astext(the_geom) FROM $1 
                                                           ^

Also I tryed create function foo() whitch returns some varchar (in my case name of table) and then i tryed call it in other function like name of table. Example:

CREATE OR REPLACE FUNCTION pokus1() RETURNS int4 AS $$
BEGIN
  PERFORM AsText(the_geom) FROM foo();
  RETURN 0;
END;
$$ LANGUAGE PLPGSQL;

And I received this errors:
SELECT pokus1();
ERROR: column "the_geom" does not exist
CONTEXT: SQL statement "PERFORM AsText(the_geom) FROM foo()"
PL/pgSQL function "pokus1" line 2 at SQL statement 

The column the_geom EXIST inside the table which is represented by my function foo(). When I displace foo() by same name of the table it works. 

Thank you for your help. 




Home | Main Index | Thread Index

Privacy Policy | About PostgreSQL
Copyright © 1996 – 2012 PostgreSQL Global Development Group