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?
This question is related to
database
postgresql
stored-procedures
plpgsql
postgresql-9.1
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.
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;
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();
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, ...
Source: Stackoverflow.com