function - tabla - sintaxis para procedimientos almacenados en postgresql

Funciones PL/pgSQL: Cómo devolver una tabla con una sentencia de ejecución

Question

Tengo esta función PL / pgSQL que debe devolver información de algunos usuarios.

CREATE OR REPLACE FUNCTION my_function(user_id integer)
  RETURNS TABLE(id integer, firstname character varying
                          , lastname  character varying) AS
$$
DECLARE
    ids character varying;
BEGIN
    ids := '''';

    --Some code which build the ids string, not interesting for this issue

    RETURN QUERY EXECUTE ''SELECT users.id, users.firstname, users.lastname
    FROM public.users WHERE ids IN ('' || ids || '')'';
END;
$$ LANGUAGE plpgsql;

El problema al que me enfrento es que el resultado de la función es una tabla de columnas única como esta:

╔═══╦═════════════════════╗
║   ║my_function          ║
╠═══╬═════════════════════╣
║ 1 ║ (106,Ned,STARK)     ║
║ 2 ║ (130,Rob,STARK)     ║
╚═══╩═════════════════════╝

Mientras esperaba:

╔═══╦════════════╦════════════╦═════════════╗
║   ║ id         ║ firstname  ║ lastname    ║
╠═══╬════════════╬════════════╬═════════════╣
║ 1 ║ 106        ║ Ned        ║ STARK       ║
║ 2 ║ 103        ║ Rob        ║ STARK       ║
╚═══╩════════════╩════════════╩═════════════╝

Creo que (pero no estoy seguro) el problema proviene de la sentencia EXECUTE , pero no puedo ver cómo hacerlo de otra manera.

¿Algunas ideas?

Answer 1

¿Cómo estás ejecutando esa función? Funciona como una declaración de selección.

Crear una tabla: public.users

create table public.users (id int, firstname varchar, lastname varchar);

Insertar algunos registros:

insert into public.users values (1, ''aaa'',''bbb''),(2,''ccc'',''ddd'');

función: my_function

CREATE OR REPLACE FUNCTION my_function(user_id integer) RETURNS TABLE(id integer, firstname character varying, lastname character varying) AS $$
    DECLARE
        ids INTEGER[];
    BEGIN
         ids := ARRAY[1,2];
         RETURN QUERY
             SELECT users.id, users.firstname, users.lastname
             FROM public.users
             WHERE users.id = ANY(ids);
    END;
$$ LANGUAGE plpgsql;

Ahora puedes usar con *

select * from my_function(1);

Resultado de la consulta

 id | firstname | lastname 
----+-----------+----------
  1 | aaa       | bbb
  2 | ccc       | ddd

O también con nombres de columna

select id,firstname,lastname from my_function(1);

Resultado

 id | firstname | lastname 
----+-----------+----------
  1 | aaa       | bbb
  2 | ccc       | ddd

Answer 2

Llamar a la función de esa manera:

select * from  my_function(123);

No sólo con seleccionar. Lo hice y funciona

Answer 3

http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/

hay una diferencia en la salida recibida de la función dependiendo de la sintaxis de la selección:

select * from myfunction();

y

select myfunction();