Lists: | pgsql-novice |
---|
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: | 2005-08-28 21:43:00 |
Message-ID: | 200508282343.16425@centrum.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
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.
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Jaromír Kamler <kamler(at)centrum(dot)cz> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: question - plpgsql and query on table given by variable |
Date: | 2005-08-28 21:55:45 |
Message-ID: | 20050828215545.GA42193@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
On Sun, Aug 28, 2005 at 11:43:00PM +0200, Jaromír Kamler wrote:
> 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.
See "Executing Dynamic Commands" in the PL/pgSQL documentation.
Here's a link to the documentation for the latest version of
PostgreSQL (but use the documentation for whatever version you're
running):
http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
--
Michael Fuhr