Postgres function/procedure won't return value

Question

I'm new to PostgreSQL, coming from SQL server and I'm having trouble creating a function and/or procedure. First of all, may I just clarify that the only difference between a function and a procedure is indeed the support for transactions?

Now going back to my problem, I want a simple func/proc that returns the current connected user. Not using it anyhow, so any other example would work, it's only for learing the syntax as pgsql is different from t-sql. Here is my code, which I took from a tutorials website:

CREATE OR REPLACE FUNCTION dev.get_user()
RETURNS VARCHAR AS $user$
DECLARE
    $user$ VARCHAR;
BEGIN
    SELECT current_user AS user;
    RETURN user; 
END;
$user$ LANGUAGE plpgsql;

I'm getting this and I don't know why:

ERROR:  syntax error at or near "VARCHAR"
LINE 4:  $user$ VARCHAR;

If I remove line 4, it creates it successfully, but when I run it, it returns this error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

I'm on pgsql 12.3, if it makes any difference. Thank you.

user1822 · Accepted Answer · 2020-07-12 12:41:41Z

2

may I just clarify that the only difference between a function and a procedure is indeed the support for transactions?

No, the biggest difference is that functions are intended to return something while procedures are not.

In PL/pgSQL, the result of a SELECT statement needs to be stored somewhere (which is what the error message is telling you).

Variables are not defined with a $ so the second $user$ ends the string started with the first $user$ which is why you get the first error.

So the correct way to write this would be:

CREATE OR REPLACE FUNCTION dev.get_user()
  RETURNS VARCHAR AS $user$
declare
  l_user varchar
begin
  select current_user
     into l_user;
  return l_user;
end;
$user$
language plpgsql;

The SELECT isn't really necessary, you can assign a variable directly if you call a function (which current_user is):

begin
   l_user := current_user
   return l_user;
end;

The assignment using := is faster then a SELECT .. INTO.

Note that the whole function can be simplified to:

CREATE OR REPLACE FUNCTION dev.get_user()
  RETURNS VARCHAR 
AS 
$user$
  select current_user;
$user$
language sql;

For more details see the PL/pgSQL reference

answered Jul 12, 2020 at 12:41

user1822

thank you very much, it's a lot clearer now. just a quick question though: the $user$ "tags" in your example (and mine) don't seem to relate to anything specific. can they be anything? can they be blank, so I'm left with $$ only? I don't see their value...

Chris
– Chris

2020-07-12 12:53:21 +00:00
Commented Jul 12, 2020 at 12:53
nevermind, I managed to read a few things in the documentation. those names define block boundaries as you can have more than one block in each func/proc. I managed to get my example working in more than one way, I guess I have to bite the bullet and read the entire documentation on syntax. thank you.

Chris
– Chris

2020-07-12 13:06:11 +00:00
Commented Jul 12, 2020 at 13:06
That's called dollar quoting - the body of a function or procedure is actually a string, but using dollar quoting makes it easier to write

user1822
– user1822

2020-07-12 13:31:36 +00:00
Commented Jul 12, 2020 at 13:31

Add a comment |

Stack Exchange Network

Postgres function/procedure won't return value

1 Answer 1

Your Answer

Hot Network Questions

Postgres function/procedure won't return value

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions