Store query result in a variable using in PL/pgSQL
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.
As long as you are assigning a single variable, you can also use plain assignment in a plpgsql function:
name := (SELECT t.name from test_table t where t.id = x);Or use SELECT INTO like @mu already provided.
This works, too:
name := t.name from test_table t where t.id = x;But better use one of the first two, clearer methods, as @Pavel commented.
I shortened the syntax with a table alias additionally.
Update: I removed my code example and suggest to use IF EXISTS() instead like provided by @Pavel.
The usual pattern is EXISTS(subselect):
BEGIN IF EXISTS(SELECT name FROM test_table t WHERE t.id = x AND t.name = 'test') THEN --- ELSE --- END IF;This pattern is used in PL/SQL, PL/pgSQL, SQL/PSM, ...