Обсуждение: Issue with PERFORM

Hello,
Environment:

Postgresql: 9.1.5
OS: CentOS 64bit 6.3(final)

Problem:

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 "nm_create_friend" line 3 at
SQL statement
SQL statement "SELECT NM_create_friend($1, friend_id)"
PL/pgSQL function "nm_create_friends" line 9 at PERFORM

and here's the actual function:
CREATE FUNCTION nm_create_friends(user_id uuid, friend_ids
text[]) RETURNS void
    LANGUAGE plpgsql
    AS $_$
DECLARE
        friend_id text;
BEGIN
        FOREACH  friend_id in array $2
        LOOP
                PERFORM NM_create_friend($1, friend_id);
  <--- this is the error line
        END LOOP;
END;
$_$;

Just in case here's the function that gets called:

CREATE FUNCTION nm_create_friend(user_id uuid, friend_id
text) RETURNS void
    LANGUAGE plpgsql
    AS $_$
BEGIN
SELECT f.friend_id
FROM
friends AS f
WHERE f.user_id = $1 AND f.friend_id = $2;
IF NOT FOUND THEN
INSERT INTO friends (user_id, friend_id) values($1,$2);
END IF;
END;
$_$;



For some reason, psql thinks that PERFORM
NM_create_friend($1, friend_id); function is using a
SELECT. Any ideas on how to fix this issue?
Yury Peskin

"Yury Peskin" <ypeskin@cycle-inc.com> wrote:

> 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 "nm_create_friend" line 3 at
> SQL statement

> CREATE FUNCTION nm_create_friend(user_id uuid, friend_id
> text) RETURNS void
>     LANGUAGE plpgsql
>     AS $_$
> BEGIN
> SELECT f.friend_id
> FROM
> friends AS f
> WHERE f.user_id = $1 AND f.friend_id = $2;
> IF NOT FOUND THEN
> INSERT INTO friends (user_id, friend_id) values($1,$2);
> END IF;
> END;
> $_$;

> For some reason, psql thinks that PERFORM
> NM_create_friend($1, friend_id); function is using a
> SELECT.

No, it thinks that within the nm_create_friend() function there is a
SELECT which isn't assigning the value to anything.  And it's right.
It subsequently shows you where that function is being called from,
but that's not as relevant.

> Any ideas on how to fix this issue?

The HINT says it all.

-Kevin

Hi,
Problem is inside nm_create_friend(uuid, text) - return type is VOID but there is a SELECT inside. There is no "space" for produced rows.

My suggestion is to add record variable and use SELECT INTO then check if vairalble is empty or not.

Regards,
Bartek

If You need this SELECT to check if rows exist  or not, it is better to use IF NOT EXISTS (SELECT ...) THEN...

Regards
Bartek