Store query result in a variable using in PL/pgSQL

Question

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?

Solution 1

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

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 involving SELECT, is 10x faster, still.)
• SELECT INTO also sets the special variable FOUND, while plain assignment does not. You may want one or the other.
• SELECT INTO can also assign multiple variables at once. See:
  • SELECT INTO with more than one attribution

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

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

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();

Demo:

View more solutions

Comments

[Pavel Stehule]

This is not good idea - this feature is not documented and it is ugly

[Pavel Stehule]

PL/pgSQL allows mix of SQL and PL - and sometimes you can create really strange creatures, but is better mix PL and SQL cleanly - in isolated statements.

[Erwin Brandstetter]

@PavelStehule: I agree, your form is preferable.

[Dao Lam]

What if I need multiple variables. Like select test_table.name, test_table.id, test_table.ssn?

[mu is too short]

@DaoLam: From the documentation I liked to: "The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables."

[Dao Lam]

@muistooshort so you're saying i can do the same and i can use name.id, name.ssn to retrieve? I tried it with IF EXISTS but didn't work: IF EXISTS (select * into name from test_table...))

[mu is too short]

@DaoLam Why are you combining INTO with IF EXISTS? Maybe you should ask a new question so that you can explain what you're trying to do.

[Dao Lam]

@muistooshort Thank you! My question is here: stackoverflow.com/questions/28886830/…

[Mitaksh Gupta]

@muistooshort this way is not working when the query is returning an array. Is there an alternative to this in case of arrays? Here is my question on the same : stackoverflow.com/questions/34194818/…

[SENHAJI RHAZI Hamza]

Actually i do prefere your syntax, but the problem is when you wana handle errors, your statement doesnt send FOUND to true in the opposite of the select into statement, checkout (postgresql.org/docs/9.1/plpgsql-statements.html)

[Fabien Snauwaert]

Note the ability to add TEMPORARY before the table name to create a temporary table. (Else, one may want to use a DROP TABLE IF EXISTS test_table; and keep an eye out for unnecessary tables.)

[Grengas]

There is no example in documentation (or I missed it), but as @muistooshort noted, you can select into multiple variables with single select: SELECT test_table.column1, test_table.column2 INTO variable1, variable2 FROM test_table WHERE id = x;

[blissweb]

Just FYI I just found out that in my old version of Postgres that NONE of these solutions work if followed by a Dynamic Query. If your main query is dynamic, you have to make the first query dynamic also using EXECUTE .... INTO variable. This took me a looong time to figure out.

[Edward Brey]

The Postgres docs refer to this syntax as SELECT select_expressions INTO (which does not create a table), not SELECT INTO (which does). More info

[mu is too short]

@EdwardBrey Thanks for the clarification.