Hi,
What is the correct way of writing plpgsql function which needs return
columns from multiple tables?
e.x.:
SELECT email FROM emails WHERE id = 1
SELECT backend FROM backends WHERE id = 1
I need plpgsql function return both email and backend in one line, like:
SELECT email, backend FROM ...
I do like this:
CREATE OR REPLACE FUNCTION get_user_data( INT )
RETURNS SETOF RECORD AS $$
DECLARE
v_email RECORD;
v_backend RECORD;
BEGIN
SELECT email
INTO v_email
FROM emails
WHERE id = $1;
SELECT backend
INTO v_backend
FROM backends
WHERE id = $1;
RETURN QUERY SELECT v_email AS email,
v_backend AS backend;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
and then doing selects:
SELECT * FROM get_user_data('${id}') AS (email VARCHAR, backend VARCHAR)
Is it okay, there will be a lot of those queries?
--
regards,
Artis Caune
<----. CCNA
<----|====================
<----' didii FreeBSD
