Re: EXECUTE query INTO problem

2008/11/25 Tk421 <vrobador(at)gmail(dot)com>:
> I've got a problem with a function: It receives two parameters, the first,
> the table name, and the second, a where condition. The function looks like
> this:
>
> DECLARE
> cod bigint;
> query TEXT;
>
> BEGIN
> query = 'SELECT codigo FROM ' || $1 || ' WHERE ' || $2;
>
> EXECUTE query INTO cod;
> ·
> ·
> ·
> END;
>
> I've alwais get the same error, in the EXECUTE sentence: it says: Error at
> or near NULL at character X
>
> I've also tried declaring cod as row, but the error is the same.
>
> Anybody can help me?
>
> Thank you very much
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

CREATE OR REPLACE FUNCTION test(character varying, character varying)
RETURNS integer AS
$BODY$declare
r_int int;
q varchar;
begin

if $1 is not null and $2 is not null then

q = 'select p1 from '||$1||' where '||$2;
execute q into r_int;
return r_int;
else
return null;
end if;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION test(character varying, character varying) OWNER TO merlin;

and simple table ;]

merlin=> \d t1
Table "public.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
p1 | integer |
p2 | character varying(32) |

merlin=> insert into t1 values(2, 'abc');INSERT 0 1
merlin=> select test('t1', ' p2= ''abc''')

;
test
------
2
(1 row)

merlin=> select test(null, ' p2= ''abc''')

;
test
------

(1 row)

And all its works

--
Serdecznie pozdrawiam

Pawel Socha
pawel(dot)socha(at)gmail(dot)com

programista/administrator

perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{
a%%s%%$_%ee'