How to write multiple queries in a single function in postgresql

Question

I need to write a function in Postgresql where the function will return a table with three columns. These three columns will be populated from three different queries. I want to know how to club these three queries in a single function.

CREATE OR REPLACE FUNCTION ytd(MMYY character varying, MMYY1 character varying)
  RETURNS TABLE(name character varying, ratio bigint, ratio1 bigint) AS
$BODY$

BEGIN
RETURN query
SELECT col1 as name from t1, 
Select col2 as ratio from t2,
Select col3 as ratio1 from t3


END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION ydt2(MMYY character varying, MMYY1 character varying)
  OWNER TO postgres;

Same parameter will be passed in each sql

Why don't you use JOIN if you have relation between these tables? — HardikS
– HardikS, Commented Jul 16, 2015 at 13:03
I want quer1 goes to name and so on...I am new in database..so i want to know is it possible to club multiple queries in single function? — user19120
– user19120, Commented Jul 16, 2015 at 13:28
Could you provide more information about the three table structures? Put the code in this way you could also susbstitute the FUNCTION with a PROCEDURE and make use of three output parameters. — sblandin
– sblandin, Commented Jul 16, 2015 at 15:11

Robins Tharakan · Accepted Answer · 2015-07-16 16:38:57Z

3

If the method of how you JOIN the three data-sets is irrelevant (and as you say they are unrelated), then merging the three data-sets should be quite trivial (See SQL Fiddle).

This SQL (below) should exemplify with an example:

CREATE TABLE a (a1 INTEGER);
CREATE TABLE b (b1 INTEGER);
CREATE TABLE c (c1 INTEGER);

INSERT INTO a VALUES(11), (12);
INSERT INTO b VALUES(21), (22);
INSERT INTO c VALUES(31), (32);

WITH 
  aa AS (SELECT row_number() OVER () AS r, * FROM a),
  bb AS (SELECT row_number() OVER () AS r, * FROM b),
  cc AS (SELECT row_number() OVER () AS r, * FROM c)
SELECT r, a1, b1, c1
FROM aa
  JOIN bb
    USING (r)
  JOIN cc
    USING (r)

answered Jul 16, 2015 at 16:38

Robins Tharakan

2,52122 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to write multiple queries in a single function in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related