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$