Postgresql – Postgres function/procedure won’t return value

functionsplpgsqlpostgresqlstored-procedures

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.

Best Answer

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

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;

Mysql – Returning true or false in a custom function

I found the error to have been my varchar

CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar

which i changed to

CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar(255) and worked.

Best Answer

Related Solutions

Postgresql – In PostgreSQL, is there a type-safe first() aggregate function

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Mysql – Returning true or false in a custom function

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`