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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
