Issue with PERFORM

[Yury Peskin]

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

[Kevin Grittner]

Post by Yury Peskin
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.

Post by Yury Peskin
Any ideas on how to fix this issue?

The HINT says it all.

-Kevin

[Bartosz Dmytrak]

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

[Bartosz Dmytrak]

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

Regards
Bartek