Store query result in a variable using in PL/pgSQL
DatabasePostgresqlStored ProceduresPlpgsqlPostgresql 9.1Database Problem Overview
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?
Database Solutions
Solution 1 - Database
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.
Solution 2 - Database
To assign a single variable, you can also use plain assignment in a PL/pgSQL code block, with a scalar subquery to the right:
name := (SELECT t.name from test_table t where t.id = x);
Effectively the same as SELECT INTO
like @mu already provided, with subtle differences:
SELECT INTO
is slightly faster in my tests on Postgres 14.
(Plain assignment of a constant, without involvingSELECT
, is 10x faster, still.)SELECT INTO
also sets the special variableFOUND
, while plain assignment does not. You may want one or the other.SELECT INTO
can also assign multiple variables at once. See:
Notably, this works, too:
name := t.name from test_table t where t.id = x;
A SELECT
statement without leading SELECT
. But I would not use this hybrid. Better use one of the first two, clearer, documented methods, as @Pavel commented.
Solution 3 - Database
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, ...
Solution 4 - Database
> Create Learning Table:
CREATE TABLE "public"."learning" (
"api_id" int4 DEFAULT nextval('share_api_api_id_seq'::regclass) NOT NULL,
"title" varchar(255) COLLATE "default"
);
> Insert Data Learning Table:
INSERT INTO "public"."learning" VALUES ('1', 'Google AI-01');
INSERT INTO "public"."learning" VALUES ('2', 'Google AI-02');
INSERT INTO "public"."learning" VALUES ('3', 'Google AI-01');
> Step: 01
CREATE OR REPLACE FUNCTION get_all (pattern VARCHAR) RETURNS TABLE (
learn_id INT,
learn_title VARCHAR
) AS $$
BEGIN
RETURN QUERY SELECT
api_id,
title
FROM
learning
WHERE
title = pattern ;
END ; $$ LANGUAGE 'plpgsql';
> Step: 02
SELECT * FROM get_all('Google AI-01');
> Step: 03
DROP FUNCTION get_all();
Solution 5 - Database
Per Executing a Query with a Single-Row Result, use this syntax:
SELECT select_expressions INTO [STRICT] target FROM ...
where target
can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields.
Unlike the SELECT INTO
, SELECT select_expressions INTO
does not create a table.
In your example, you have a single simple variable name
, so the select statement would be:
SELECT test_table.name INTO name FROM test_table WHERE test_table.id = x;
Solution 6 - Database
You can use the following example to store a query result in a variable using PL/pgSQL:
select * into demo from maintenanceactivitytrack ;
raise notice'p_maintenanceid:%',demo;