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.

Why did you tag sql server and a specific version of sql server when you are not using sql server at all? — Sean Lange
– Sean Lange, Commented Mar 30, 2018 at 19:33
See: Is it possible to use a variable and not specify a return type in postgreSQL? — klin
– klin, Commented Mar 30, 2018 at 20:08

Erwin Brandstetter · Accepted Answer · 2018-04-01 01:25:05Z

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

BShaps · Accepted Answer · 2018-04-03 00:06:50Z

0

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

edited Apr 3, 2018 at 0:06

answered Mar 30, 2018 at 19:59

BShaps

1,4248 silver badges18 bronze badges

6 Comments

Abelisto Over a year ago

It could be done without creating additional type but using OUT parameters: CREATE OR REPLACE FUNCTION function_name(OUT l_pin INTEGER, OUT l_pin1 INTEGER) AS $$ .... For example: create function foo (out x int, out y int) language sql as $$ select 1, 2 $$;

BShaps Over a year ago

@Abelisto You certainly can if you're on version 8.1+, but for me I prefer the creation of a type because it makes the code cleaner in my opinion.

Erwin Brandstetter Over a year ago

Creating a type for this seems excessive. (And who would still be running Postgres older than version 8.1 - released in 2005?)

BShaps Over a year ago

@ErwinBrandstetter It was for the sake of completeness, how does pointing out the version requirements for a feature hurt anything? Also, downvoting a correct answer because you think one line of code is excessive is poor form.

Erwin Brandstetter Over a year ago

@BShaps: Granted, your answer works. So I removed the downvote. It's still bad advice IMO. And it's misleading to claim we'd need to create a new type.

|

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Linked

Related