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$
Best Answer
Just add the variable to the
SELECT
list: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 withOUT
parameters instead ofRETURNS TABLE
since you only return exactly 1 row anyway.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: