How to return a table from a POSTGRES function?

Question

How to return a table from a POSTGRES function?

Navigation

#1 by (1 votes)

1

Hello, I'm trying to create a function in postgres, which at first is all correct, taking the return from the function.

When I run the function, it is returning all rows, but all in only one field as a string. And I need it to come in the table format.

How come:

HowshouldIcome:

MY FUNCTION

CREATE OR REPLACE FUNCTION public.fc_comparar_comandos(var1 text,var2 text)
RETURNS TABLE ( p_cmap_id integer,
            p_cmap_cmd_id integer,
            p_cmd_nome text,
            p_cmap_parametro character varying,
            p_cmap_part_number character varying,
            p_cmap_ordem integer,
            p_cmap_level integer,
            p_teste text,
            p_cmd_param text,
            p_testado integer
) 
LANGUAGE 'plpgsql'
COST 1
VOLATILE 
ROWS 1000
AS $BODY$

DECLARE
    p_cmd     ALIAS FOR var1;
    p_pNumber ALIAS FOR var2;

BEGIN

RETURN QUERY

SELECT   cmap_id,
         cmap_cmd_id,
         cmd_nome,
         cmap_parametro,
         cmap_part_number,
         cmap_ordem,
         cmap_level,
         teste,
         (cmd.cmd_nome || ' ' ||cmd_aprovacao.cmap_parametro) as cmd_param,
         CASE WHEN teste IS NULL THEN 0 ELSE 1 END as testado
FROM cmd_aprovacao
INNER JOIN cmd ON cmd.cmd_id = cmd_aprovacao.cmap_cmd_id
LEFT JOIN (
            SELECT UNNEST(
                STRING_TO_ARRAY(p_cmd, ',')
               ) as teste
            ) t ON t.teste = (cmd.cmd_nome || ' ' ||cmd_aprovacao.cmap_parametro)
WHERE cmap_part_number = p_pNumber
AND cmap_level = (  SELECT MAX(cmap_level)
                    FROM cmd_aprovacao 
                    WHERE cmap_part_number = p_pNumber
                 )
ORDER BY cmap_ordem;

END

$BODY$;

sql java postgresql

asked by anonymous 20.03.2018 / 19:53

1 answer

JQGrid call a function with a click on a cell passing the line id Does anyone help me with this?

score 1 · Accepted Answer

The problem is not in its function, it is in the way it is being called.

When you call your function this way:

SELECT public.fc_comparar_comandos( 'ABCD', 'XPTO' );

Postgres returns a single field of type RECORD .

To get the "separate" columns, how about calling your function this way:

SELECT * FROM public.fc_comparar_comandos( 'ABCD', 'XPTO' );

Or:

SELECT
    p_cmap_id,
    p_cmap_cmd_id,
    p_cmd_nome,
    p_cmap_parametro,
    p_cmap_part_number,
    p_cmap_ordem,
    p_cmap_level,
    p_teste,
    p_cmd_param,
    p_testado
FROM
    public.fc_comparar_comandos('XPTO','ABCD');