2

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$
  • 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 '16 at 1:52
  • 1
    Please remember to declare your version of Postgres. – Erwin Brandstetter Nov 22 '16 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 '16 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 '16 at 2:50
  • 1
    @mendosi: I suspected as much. – Erwin Brandstetter Nov 22 '16 at 3:09
1

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) 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;

I simplified with table aliases.

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

You might use RETURNS RECORD combined with OUT parameters instead of RETURNS TABLE since you only 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) 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 is redundant noise which can be omitted in this case.

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

  • 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 '16 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. – Erwin Brandstetter Nov 22 '16 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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