0

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.

1

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

3
  • 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 Jul 12 '20 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 Jul 12 '20 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 – a_horse_with_no_name Jul 12 '20 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.