Wednesday, September 10, 2008

[GENERAL] plpgsql return select from multiple tables

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

No comments: