Store query result in a variable using in PL/pgSQL

asked13 years ago
last updated8 years ago
viewed340.4k times
Up Vote189Down Vote

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL?

I have a function:

CREATE OR REPLACE FUNCTION test(x numeric)
RETURNS character varying AS
$BODY$
DECLARE
name   character varying(255);
begin
 name ='SELECT name FROM test_table where id='||x;

 if(name='test')then
  --do somthing
 else
  --do the else part
 end if;
end;
return -- return my process result here
$BODY$
LANGUAGE plpgsql VOLATILE

In the above function I need to store the result of this query:

'SELECT name FROM test_table where id='||x;

to the variable name.

How to process this?