Re: FW: 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