Обсуждение: FW: execute dynamic strings. need help.

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.

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

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

Stefan.Ardeleanu@siveco.ro wrote:
> 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?

Well, you could cast all the output columns to type text, then you'd
only need to know how many columns there were. Otherwise, no - the
system needs to know what types are involved either from the function or
the query itself.

> In JDBC, when calling this function, you have to know these column types?

No idea if it works with JDBC I'm afraid. You'll have to give it a try.

--
   Richard Huxton
   Archonet Ltd