Início / dba / Perguntas / 155955
Accepted
Dan
Asked: 2016-11-22 16:12:36 +0800 CST

Tabela de retorno da função Postgres com coluna extra

  • 772

Novato no Postgres aqui..

Tenho uma função Postgres/plpgsql que vai retornar uma tabela. Quero retornar tudo de uma consulta mais um valor lógico que crio e retorno junto com ela.

No entanto, depois de pesquisar, não consigo descobrir como posso retornar esses dados junto com um valor lógico que gero dinamicamente na consulta.

CREATE OR REPLACE FUNCTION public.sp_user_get_credentials_by_email(email_address character varying)
RETURNS TABLE(credential_id integer, user_id integer, password_hash character varying, password_salt character varying, created_at timestamp without time zone, last_updated_at timestamp without time zone, logical_value_return SMALLINT)
 LANGUAGE plpgsql
AS $function$

DECLARE
sproc_logical_value_return SMALLINT;

BEGIN

-- also want to return logical_value_return along with the query below
-- ex: sproc_logical_value_return = (2)::INT2; how do I add this as a column logical_value_return along with the query below?

  RETURN QUERY
    SELECT
      myapp_users_credentials.credential_id,
      myapp_users_credentials.user_id,
      myapp_users_credentials.password_hash,
      myapp_users_credentials.password_salt,
      myapp_users_credentials.created_at,
      myapp_users_credentials.last_updated_at
    FROM
      myapp_users_credentials
    JOIN myapp_contacts_assoc ON
      myapp_contacts_assoc.user_id = myapp_users_credentials.user_id AND
      myapp_users_credentials.expired_at IS NULL
    JOIN myapp_contacts ON
      myapp_contacts.contact_id = myapp_contacts_assoc.contact_id AND
      myapp_users_credentials.expired_at IS NULL
    WHERE
      myapp_contacts.value = $1 AND
      myapp_contacts.type = 1 AND
      myapp_contacts.is_primary = 1
    LIMIT 1;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Credentials not found';
  END IF;

END
$function$
postgresql functions

1 respostas

  1. Best Answer

    Basta adicionar a variável à SELECTlista:

    CREATE OR REPLACE FUNCTION public.sp_user_get_credentials_by_email(email_address varchar)
  RETURNS TABLE (credential_id integer
               , user_id integer
               , password_hash varchar
               , password_salt varchar
               , created_at timestamp
               , last_updated_at timestamp
               , logical_value_return smallint) AS
$func$
DECLARE
   sproc_logical_value_return SMALLINT := 2;  -- you can assign at declaration time
BEGIN
   RETURN QUERY
      SELECT uc.credential_id,
             uc.user_id,
             uc.password_hash,
             uc.password_salt,
             uc.created_at,
             uc.last_updated_at
             sproc_logical_value_return  -- just put it in the SELECT list
      FROM   myapp_users_credentials uc
      JOIN   myapp_contacts_assoc    ca ON ca.user_id = uc.user_id      AND uc.expired_at IS NULL
      JOIN   myapp_contacts          c  ON c.contact_id = ca.contact_id AND uc.expired_at IS NULL
      WHERE  c.value = $1
      AND    c.type  = 1
      AND    c.is_primary = 1
      LIMIT  1;

   IF NOT FOUND THEN
      RAISE EXCEPTION 'Credentials not found';
   END IF;
END
$func$  LANGUAGE plpgsql ROWS 1;

    Simplifiquei com aliases de tabela.

    Como sua função retorna uma única linha por definição, o valor também é retornado apenas uma vez.

    Você pode usar RETURNS RECORDcombinado com OUTparâmetros em vez de RETURNS TABLE, pois retorna exatamente 1 linha de qualquer maneira.

    CREATE OR REPLACE FUNCTION public.sp_user_get_credentials_by_email(
                 IN  email_address varchar
               , OUT credential_id integer
               , OUT user_id integer
               , OUT password_hash varchar
               , OUT password_salt varchar
               , OUT created_at timestamp
               , OUT last_updated_at timestamp
               , OUT logical_value_return smallint) AS
$func$
BEGIN

   logical_value_return := 2;  -- assign separately or with SELECT list

   SELECT uc.credential_id, uc.user_id, uc.password_hash, uc.password_salt, uc.created_at, uc.last_updated_at
   INTO      credential_id,    user_id,    password_hash,    password_salt,    created_at,    last_updated_at
   FROM   myapp_users_credentials uc
   JOIN   myapp_contacts_assoc    ca ON ca.user_id = uc.user_id      AND uc.expired_at IS NULL
   JOIN   myapp_contacts          c  ON c.contact_id = ca.contact_id AND uc.expired_at IS NULL
   WHERE  c.value = $1
   AND    c.type = 1
   AND    c.is_primary = 1
   LIMIT  1;

   IF NOT FOUND THEN
      RAISE EXCEPTION 'Credentials not found';
   END IF;

END
$func$  LANGUAGE plpgsql;

    RETURNS RECORDé um ruído redundante que pode ser omitido neste caso.

    A diferença sutil: você ainda pode retornar a constante lógica, mesmo quando nenhuma linha for encontrada. Outras colunas seriam NULL neste caso. Detalhes:

    • 1

relate perguntas