Store query result in a PL/pgSQL variable

Question

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?

Answer 1

1. SELECT INTO var ...

This can assign one or more variables at once and discards additional rows. [@mu already provided an example.]

2. var := (subquery)

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

Some notable difference to SELECT INTO like @mu already provided:

• 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
• SELECT INTO assigns values from the first row returned and discards additional rows silently, while this raises an exception if more than one row is returned:

  "ERROR: more than one row returned by a subquery used as an expression"

3. Hybrid assignment

This also works:

name := t.name FROM test_table t WHERE t.id = x;

A SELECT statement without leading SELECT. It's much like the above, but "no row" results in no assignment, while the subquery expression above converts "no row" to a null value and assigns that. This can matter. See:

• Special variable FOUND not set properly?

Unless you specifically need this behavior, rather stick to one of the first two, clearer, documented methods.

Answer 2

For example, you create person table, then insert 2 rows into it as shown below as shown below:

CREATE TABLE person (
  id INT,
  name VARCHAR(20)
);

INSERT INTO person (id, name) VALUES (1, 'John'), (2, 'David');

Then, you can create my_func() which can store a query result into person_name, then return it as shown below:

CREATE FUNCTION my_func()
RETURNS VARCHAR(20)
AS $$
DECLARE
  person_id public.person.id%TYPE := 2;
  person_name public.person.name%TYPE;
BEGIN
  SELECT name INTO person_name FROM person WHERE id = person_id;
  RETURN person_name;
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() returns David as shown below:

postgres=# SELECT my_func();
 my_func
---------
 David
(1 row)

And, you can create my_func() which can store a query result into person_row, then return it as shown below:

CREATE FUNCTION my_func()
RETURNS person
AS $$
DECLARE
  person_row public.person%ROWTYPE; -- Here
  -- person_row RECORD; -- Here
BEGIN
  SELECT * INTO person_row FROM person WHERE id = 2;
  RETURN person_row;
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() returns a row as shown below:

postgres=# SELECT my_func();
  my_func
-----------
 (2,David)
(1 row)

Answer 3

Many answers here omit important parts of using functions, and given the popularity I think many arrive here looking for a quick overall primer on using functions.

Here's an example of using functions in postgres (including declaration, variables, args, return values, and running). Below is an over-baked way of updating the tweet on the bottom right "blurb" with "hello world".

id (serial)	pub_id (text)	tweet (text)
1	abc	hello world
2	def	blurb

-- Optional drop if replace fails below.
drop function if exists sync_tweets(text, text);

create or replace function sync_tweets(
    src_pub_id text, -- function arguments
    dst_pub_id text
) returns setof tweets as -- i.e. rows. int, text work too
$$
declare
    src_id    int; -- temp function variables (not args)
    dest_id   int;
    src_tweet text;
begin
    -- query result into a temp variable
    src_id := (select id from tweets where pub_id = src_pub_id);

    -- query result into a temp variable (another way)
    select tweet into src_tweet from tweets where id = src_id;

    dest_id := (select id from tweets where pub_id = dst_pub_id);
    update tweets set tweet=src_tweet where id = dest_id;

    return query -- i.e. rows, return 0 with return int above works too
        select * from tweets where pub_id in (src_pub_id, dst_pub_id);
end
$$ language plpgsql; -- need the language to avoid ERROR 42P13

-- Run it!
select * from sync_tweets('abc', 'def');

/*
  Outputs
   __________________________________________________ 
  |  id (serial)  |  pub_id (text)  |  tweet (text)  |
  |---------------|-----------------|----------------|
  |  1            |  abc            |  hello world   |
  |  2            |  def            |  blurb         |
  --------------------------------------------------
*/

Answer 4

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.

Answer 5

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;

Answer 6

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;

Answer 7

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:

Answer 8

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, ...