[SOLVED] Store query result in a PL/pgSQL variable

Store query result in a PL/pgSQL variable

How to assign the result of a query to a variable in PL/pgSQL?

I have a function:

CREATE OR REPLACE FUNCTION test(x numeric)
  RETURNS character varying
  LANGUAGE plpgsql AS
$BODY$
DECLARE
   name character varying(255);
BEGIN
   name = 'SELECT name FROM test_table where id = ' || x;
  
   if name = 'test' then
      -- do something
   else
      -- do something else
   end if;

   return ...  -- return my process result here
END
$BODY$;

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

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

How to process this?

Solution

I think you're looking for SELECT select_expressions INTO:

select test_table.name into name from test_table where id = x;

That will pull the name from test_table where id is your function's argument and leave it in the name variable. Don't leave out the table name prefix on test_table.name or you'll get complaints about an ambiguous reference.