How to get a output of a variable from function in postgresql

Question

I am new to PostgreSQL. I have the query:

---------
DO
$$
DECLARE
    l_pin INT;
    l_pin1 int;
BEGIN
    l_pin := 3;
    l_pin1 := 4;

select l_pin,l_pin1;
END;
$$
LANGUAGE PLPGSQL;
--------------------------

from above query am getting an error as

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 9 at SQL statement
SQL state: 42601

I need to get the values of l_pin and l_pin1 as output.

Answer 1

What you have there is a DO statement, not a "query" nor a "function". DO statements cannot return anything at all.

• How to perform a select query in a DO block?

The displayed error is because you cannot call SELECT in a plpgsql code block without assigning the result. To actually return values from a plpgsql function, use some form of RETURN (explicitly or implicitly). As minimal example:

CREATE OR REPLACE FUNCTION foo(OUT l_pin int, OUT l_pin1 int)
  RETURNS record AS  -- RETURNS record is optional because of OUT parameters
$func$
BEGIN
   l_pin := 3;
   l_pin1 := 4;

   RETURN;  -- RETURN is optional here because of OUT parameters
END
$func$  LANGUAGE plpgsql;

SELECT * FROM foo();

Related:

• Can I make a plpgsql function return an integer without using a variable?

• SELECT or PERFORM in a PL/pgSQL function

• Returning from a function with OUT parameter

Answer 2

First you can create a new type that can hold multiple values:

CREATE TYPE type_name AS (l_pin INTEGER, l_pin1 INTEGER);

Then you can do something like:

CREATE OR REPLACE FUNCTION function_name()
RETURNS type_name AS $$
  DECLARE
    result type_name;
  BEGIN
    /* Code that puts those values into type_name object 
    i.e. 
    result.l_pin := 3;
    result.l_pin1 := 4;
    */ 
    return result ;
  END
$$ language plpgsql