Thank you very, very much. It works.
Do you know if exista another similar possibility do resolve this problem
without need to know the columns types?
In JDBC, when calling this function, you have to know these column types?
Thanks in advanced for everything.
Stefan Ardeleanu
Siveco
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: 22 februarie 2005 17:44
To: Stefan.Ardeleanu@siveco.ro
Cc: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] execute dynamic strings. need help.
Stefan.Ardeleanu@siveco.ro wrote:
> I have a function with 3 parameters (select clause, where clause and order
> by clause (last two are optionally clauses). This clauses apply to a given
> table. For example, the table table1
>
> and the function table1_rwc (read by where clause).
> Given, let's say, the followings values select_clause = '*', where_clause
> 'id = 1' and the order_by clause = 'id', it will be generate the result set
> of the following query:
>
> select * from Table1 where id = 1 order by id
>
> I know I must use execute and prepare syntax, but I don't know how to create
> the function.
> Can you help me, please.
Something like (not tested):
CREATE FUNCTION my_exec(text,text,text) RETURNS SETOF RECORD AS '
DECLARE
qry text;
r RECORD;
BEGIN
qry := ''SELECT '' || $1 || '' FROM Table1 WHERE '' || $2 || '' ORDER
BY '' || $3;
FOR r IN EXECUTE qry LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
Then something like:
SELECT * FROM my_exec('*','id=1','id') AS (a int, b text, c, date);
That's assuming a,b,c have the correct types. You will need to know what
types you are returning though.
--
Richard Huxton
Archonet Ltd