Question 292224 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – "RETURNS TABLE (col record)" in a PL/pgSQL function

I have a function in PL/pgSQL that accepts a record type parameter, and returns 1 record type as well:

DROP FUNCTION IF EXISTS public.f_obtem_record_auth(record);

CREATE OR REPLACE FUNCTION public.f_obtem_record_auth(
    rec RECORD 
    -- trabalhar com apenas um aparametro do tipo record 
    -- return record 
)
RETURNS TABLE(
    /*auth TEXT
    , code INTEGER */
    auth RECORD
)
LANGUAGE plpgsql
AS $function$ 
DECLARE 
    vrecordauth RECORD;
BEGIN
    -- LOGIN IPP
    -- TRATAR ERROS, CODIGOS <> 200 OU '' PARA TODAS AS BANDEIRAS 
    IF (rec.codigo_bandeira = '00001') THEN 
        WITH  sub_primeira_req AS (
      -- my code
        ), sub_segunda_req AS (
      -- my code        
        ), sub_terceira_req AS ( 
          -- my code 
        ), sub_quarta_req AS ( 
            SELECT
            -- my code
        )
        SELECT 
            jtr."AuthProxyJWT" auth,
            sub.code
        INTO vrecordauth
        FROM sub_quarta_req sub
        , JSONB_TO_RECORD(sub.cookies) AS jtr (
            "AuthProxyJWT" TEXT
        );
          auth := vrecordauth;
        RETURN NEXT;
    -- AUTENTICAÇÃO IPP PEGAR NO NOT PAD++
    
    ELSE 
        RAISE EXCEPTION 'Código bandeira: [%] desconhecido. Entre em contato com o desenvolvedor', PCodigoEmpresa;
    END IF;
END;
$function$;

I want it to accept a record parameter, and naturally I will be able to access nested fields. Example:

rec.user
rec.password

This part is well resolved and works, the part that doesn’t seem to work is this:

RETURNS TABLE(
    /*auth TEXT
    , code INTEGER */
    auth RECORD
)

So I made a second function to test the first:

DROP FUNCTION IF EXISTS public.f_testa_login();
CREATE OR REPLACE FUNCTION public.f_testa_login()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE 
    rec             RECORD;
    VRecordAuth     RECORD; 
BEGIN
    SELECT 
        CAST('' AS TEXT) usuario
        , CAST('' AS TEXT) senha  
        , CAST('' AS TEXT) auth
        , CAST('' AS TEXT) cookies
        , CAST('' AS TEXT) ltpatoken
        , CAST('' AS TEXT) subjsession
        , CAST('' AS TEXT) javax
        , CAST('' AS TEXT) codigo_empresa
        , CAST('' AS TEXT) codigo_fornecedor
        , CAST('' AS TEXT) codigo_produto
    INTO  VRecordAuth;
    
    FOR rec IN 
        SELECT
             -- my code
        FROM    bandeira b 
            -- my code
        WHERE   e.registro_ativo 
        AND fpe.registro_ativo 
        AND pe.registro_ativo 
        AND p.registro_ativo 
        GROUP BY 
            -- my group by 
        ORDER BY 
            -- my order by 
    LOOP 
        IF (rec.nome_bandeira = 'SHELL') THEN 
            IF (VRecordAuth.usuario != rec.usuario OR VRecordAuth.senha != rec.senha OR VRecordAuth.auth = '') THEN 
                SELECT 
                    CAST(rec.usuario AS TEXT) 
                    , CAST(rec.senha AS TEXT)  
                    , auth 
                INTO VRecordAuth
                FROM f_obtem_record_auth(rec)
                AS auth_record(auth RECORD);
                RAISE NOTICE 'Usuario: %', auth_record;
            END IF;
        END IF;
    END LOOP;
END;
$function$
; 
SELECT * FROM f_testa_login()

I tried sending record type parameters (stored in var rec) and I sent this rec as a parameter in my function f_obtem_record_auth()
and I inserted the result into a variable (VRecordAuth) of type record too, to access the fields of this result later, and that’s where the problem lies, when I print VRecordAuth I get the error:

ERROR: record "auth" is not assigned yet
Detalhe: The tuple structure of a not-yet-assigned record is indeterminate.  
Onde: PL/pgSQL function f_obtem_record_auth(record) line 76 at RETURN NEXT  
SQL statement "SELECT 
                  CAST(rec.usuario AS TEXT) 
                  , CAST(rec.senha AS TEXT)  
                  , auth 
                                             FROM f_obtem_record_auth(rec)
              AS auth_record(auth RECORD)"
PL/pgSQL function f_testa_login() line 94 at SQL statement

But when I change the return from the f_obtem_recor_auth() function to:

RETURNS TABLE(
    auth TEXT
    , code INTEGER
)

everything works as expected and in my test function I can access the returns from the f_obtem_record_auth() function.

Why is this happening?
Why do I get the error mentioned above?
Why can’t I just use a table return with a record type containing several fields?

I’m a beginner in programming so be patient with me.

Tags:

2

Answers


  1. 0

    Q: Why is this happening? Why do I get the error mentioned above? Why can’t I just use a table return with a record type containing several fields?

    Note that record is not a type, but a pseudo-type.

    You may still use it to return results from a function, but not in combination with TABLE. The declaration is RETURNS SETOF record

    Here’s a very simple example

    CREATE FUNCTION pltest() RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
DECLARE r record;
BEGIN
  select 'v1' AS col1, 'v2' AS col2 into r;
  return next r;
END
$function$
;

    When calling this function from an SQL query, it is necessary to fully specify a "column definition list" in the query, so that the query planner knows the count and types of the columns from these records.

    Example:

    select * from pltest() as x(a text, b text);

 a  | b  
----+----
 v1 | v2

    See for more:

    Login or Signup to reply.
  2. 0

    Immediate fix

    The immediate problem that causes the error is buried in your function f_obtem_record_auth() here:

    SELECT 
    jtr."AuthProxyJWT" auth,
    sub.code
INTO vrecordauth
...
auth := vrecordauth;
RETURN NEXT;

    Adding another ROW wrapper makes it work:

    SELECT ROW (jtr."AuthProxyJWT", sub.code)
INTO vrecordauth

    fiddle

    Doesn’t make it a good idea though. Much too convoluted. There are simpler solutions …

    Also, I think we found a bug:

    fiddle

    Basics

    record is not an actual data type. The manual:

    Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared
    to return type record, this is not quite the same concept as a
    record variable, even though such a function might use a record
    variable to hold its result. In both cases the actual row structure is
    unknown when the function is written, but for a function returning
    record the actual structure is determined when the calling query is
    parsed, whereas a record variable can change its row structure
    on-the-fly.

    You assume:

    I want it to accept a record parameter, and naturally I will be able to access nested fields.

    No. You must pass a well-known row type (= composite type) containing all the fields that are hard-coded in the function body. Or you must cast the anonymous record to such a well-known row type, before you can access its fields.

    Cases where such a construct makes sense are few and far between. I have never had such a case. But it’s possible.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search