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 '
v ALIAS FOR $1;
SELECT AsText(the_geom) FROM v;
' LANGUAGE plpgsql;
And this is error:
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 $$
PERFORM AsText(the_geom) FROM foo();
$$ LANGUAGE PLPGSQL;
And I received this errors:
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.
pgsql-novice by date
|Next:||From: Michael Fuhr||Date: 2005-08-28 21:55:45|
|Subject: Re: question - plpgsql and query on table given by variable|
|Previous:||From: Tom Lane||Date: 2005-08-28 21:19:48|
|Subject: Re: query optimization |