Not using value error when using a SELECT statement

Question

The following select statement is part of the body of a function (returns void):

create or replace
function api.register(
    auth_agent text,
    auth_id text,
    email text
)
returns void as $$
    begin

        select v1, v2
        from auth.register(
            auth_agent::core.auth_agent,
            auth_id::text,
            email::core.domain_email
        ) as ( v1 text, v2 text);

        perform api.login(
            auth_agent := v1,
            auth_id := v2
        );    
    end;
$$ security definer language plpgsql;

Calling the function generates the following error:

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 auth.register(auth_agent,text,domain_email) line 12 at SQL statement
SQL statement "select v1, v2
        from auth.register(
            auth_agent::core.auth_agent,
            auth_id::text,
            email::core.domain_email
        ) as ( v1 text, v2 text)"
PL/pgSQL function api.register(text,text,text) line 7 at SQL statement

I'm confused. What's wrong here?

postgresql version: PostgreSQL 13.5 on x86_64-pc-linux-gnu, compiled by gcc, a ea47e69d19 p 313746c5ab, 64-bit

How come 3 fields become 2 filed. `from auth.register( auth_agent::core.auth_agent, auth_id::text, email::core.domain_email ) as ( v1 text, v2 text);`? — jian, Mar 03 '22 at 09:03
I'm using 3 inputs for the `auth.register` function. That function has 2 outputs. — Edmund's Echo, Mar 03 '22 at 15:28
ok. I got it. your issue is variable name confliction, erwin answer many times. anyway you can also use procedure. — jian, Mar 03 '22 at 15:59

Erwin Brandstetter · Answer 1 · 2022-02-18T22:33:06.263

0

Like the error message says, every SELECT needs a destination in a PL/pgSQL code block. To execute something without target use PERFORM instead of SELECT.

This should work:

DECLARE
   _v1 text;
   _v2 text;
BEGIN

SELECT INTO _v1, _v2  -- use different variable names!
             v1,  v2
FROM auth.register(
    auth_agent::core.auth_agent,
    auth_id::text,
    email::core.domain_email
) AS ( v1 text, v2 text);

PERFORM api.login(
    auth_agent := _v1,
    auth_id := _v2
);

SELECT INTO ... declares the required destination. Note how I chose different variable names to avoid naming conflicts. See:

But this should be cheaper & simpler - unless you need _v1 and _v2 later in the same code block:

PERFORM api.login(auth_agent := v1, auth_id := v2)
FROM    auth.register(auth_agent::core.auth_agent, auth_id::text, email::core.domain_email) AS (v1 text, v2 text);

We can't tell for sure from the pieces you disclosed.

answered Feb 18 '22 at 22:04

Erwin Brandstetter

605,456
145
1,078
1,228

Thank you. One thing I'm gathering here is that `select a from fn(input) as (a text)` might have two observations: (1) I'm not assigning the return value to `a` in `select a`; I have to use `into` to do that (2) the shadowing of variable names is ok in this context, but once I use `into` I have to use a different name. Is that right? – Edmund's Echo Feb 18 '22 at 22:22
@Edmund'sEcho: (1) `SELECT INTO` is *one* way (the one I would suggest for the case). There are others. Follow the links I provided. (2) Not sure what you mean by "shadowing". Details in the manual [here](https://www.postgresql.org/docs/current/plpgsql-declarations.html) and [here](https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST). (3) Always provide your *complete* function definition (even if it's not working), because parameters, variables, and all the rest matter. And your version of Postgres, obviously. – Erwin Brandstetter Feb 18 '22 at 22:31
"shadowing" is not a good way to describe it here, but I was thinking that the two `a` references in `select a from fn(input) as (a text)`, one is overwriting the other. I suspect based on your response that they must match and that the second use is there to type the variable; required, and thus names must align. – Edmund's Echo Feb 19 '22 at 02:01
The latter `a` in your example is a *column alias*, not a reference. (Only certain functions returning anonymous records *require* a full column definition list including the type.) The name in the `SELECT` list must match to reference it. – Erwin Brandstetter Feb 19 '22 at 03:01

Not using value error when using a SELECT statement

1 Answers1