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$