Re: EXECUTE query INTO problem

2008/11/25 Tk421 <vrobador@gmail.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@postgresql.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$declarer_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' VOLATILECOST 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.socha@gmail.com

programista/administrator

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