3

Newbie to Postgres here..

I have a Postgres / plpgsql function that will return a table. I want to return everything from a query plus a logical value that I create and return along with it.

However, after searching around I can't seem to figure out how I can return this data along with a logical value that I generate on the fly within the query?

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$
8
  • 1
    When you say you want to return 'everything from a query plus a logical value' do you mean that you want to have it as an extra column in the table that is returned, or do you want to return multiple result sets?
    – mendosi
    Nov 22, 2016 at 1:52
  • 2
    Please remember to declare your version of Postgres. Nov 22, 2016 at 2:18
  • @mendosi is there a reason to do one over the other? I was thinking just adding an extra column but is there a reason to go with the latter? example of each?
    – Dan
    Nov 22, 2016 at 2:31
  • No, just wanted to be clear, I would go with extra column too. Looks like the answer by Erwin suits.
    – mendosi
    Nov 22, 2016 at 2:50
  • 1
    @mendosi: I suspected as much. Nov 22, 2016 at 3:09

1 Answer 1

2

Just add the variable to the SELECT list:

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)
  LANGUAGE plpgsql ROWS 1 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$;

I simplified with table aliases.

Since your function returns a single row by definition, the value is also returned only once.

I would use RETURNS RECORD combined with OUT parameters instead of RETURNS TABLE since you return exactly 1 row anyway.

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)
  LANGUAGE plpgsql 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$;

RETURNS RECORD is redundant noise which can be omitted in this case.

The subtle difference: You can still return the logical constant, even when no row is found. Other columns would be NULL in this case. Details:

2
  • this is a great response. I have a couple more questions. * Does it make more sense to RETURN TABLE or use OUT variables for a 1 row return? Is there pros or cons to either? * Are my JOIN statements optimized? Should I checked for expired_at IS NULL in the JOIN clause or the WHERE clause? Thanks again for the assistance!
    – Dan
    Nov 22, 2016 at 2:41
  • 1
    @Dan: For exactly 1 row (not 0-1 or 1-n or 0-n) OUT params make more sense. The query planner knows the function returns exactly 1 row, while with RETURNS TABLE the added ROWS 1 is just an estimate. As for the other questions, please ask questions as questions. Comments are not the place. This query is optimized. Nov 22, 2016 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.