Declare variable set = select

Question

This probably sounds like a really stupid question, but how do I declare a variable for used in a PostgreSQL 9.3 query?

CREATE or replace FUNCTION public.test()   returns int4 AS $BODY$ DECLARE         cod_process bigint :=30001;     cod_instance bigint ;     utc_log timestamp without time zone := localtimestamp;     cod_log_type varchar(100) :='information ';     txt_log_text varchar(100):= 'start process';     txt_log varchar(100):= txt_log_text||'_'||cod_process;     set cod_instance= select max(cod_instance) as cod_instance from public.instance where public.instance.cod_process=cod_process;     BEGIN       INSERT INTO public.log (cod_process, cod_instance, utc_log,cod_log_type,txt_log)     VALUES (cod_process, cod_instance, utc_log,cod_log_type,txt_log );     RETURN 11; END; $BODY$ LANGUAGE 'plpgsql'; 

ERROR: type "cod_instance" does not exist SQL state: 42704 Character: 383 

Answer 1

You need to run the select using the into clause inside the actual code block, not in the declare block:

begin    select max(cod_instance)        into cod_instance    from public.instance     where public.instance.cod_process=cod_process;     .... end; 

It's usually not such a good idea to give variables (or parameters) the same name as columns in the table. There are certain cases where this can confuse the parser. To avoid any potential problems, try to use different names for your variables, e.g. by prefixing them (e.g. l_cod_process instead of cod_process or l_cod_instance instead of cod_instance)

More details on variable assignment can be found in the manual: http://www.postgresql.org/docs/current/static/plpgsql-statements.html