Postgres equivalent to MS-SQL SELECT DataSet needed (returning multiple tables in one query)

Question

I'm looking for a possibility to retrieve multiple DataTables (within one DataSet), using npgsql and a postgres function (stored procedure). Though my postgres function in general works already, I don't get back 2 separate tables, but only one table, containing all results of all queries (both when executing the function via pgAdmin and via npgsql / NpgsqlDataAdapter.Fill).

See my simplified sample here:

CREATE OR REPLACE FUNCTION "MyFunction"(_parameter character varying)
RETURNS SETOF "MyView" AS
$BODY$
DECLARE
BEGIN
return query SELECT * FROM "MyView" WHERE "Col1" = 'A' AND "Col2" = _parameter;
return query SELECT * FROM "MyView" WHERE "Col1" = 'B' AND "Col2" = _parameter;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

Using caller:

SELECT * FROM "MyFunction"('abc');

Isn't there ANY way to get returned a DataSet containing SEPARATE DataTables using npgsql.dll? The query must not necessarily be via a postgres function - if it wpuld be possible to get such DataSet with separate tables by simply using ngpsql ExecuteScalar that would even be preferable... Thanks for any suggestion!

Hmm. I know the code that returns multiple resultsets for NpgsqlDataReader.NextResult() fairly well (I did a rewrite on it a few years back) and NpgsqlDataAdapter.Fill() should call into that, but that code I don't know well. It could be that NpgsqlDataAdapter.Fill() handles NpgsqlDataReader.NextResult() incorrectly, and that this is a bug. — Jon Hanna
– Jon Hanna, Commented Jan 17, 2014 at 13:19
Thanks Jon Hanna,I made my function working by using NpgsqlDataReader.NextResult(). When comparing this solution with the SqlDataAdapter.Fill() method, this approach requires quite some more work and many of lines of code - however, it's working :-) — Loonis
– Loonis, Commented Jan 17, 2014 at 14:51

Pavel Stehule · Accepted Answer · 2014-01-17 15:21:33Z

0

It should not work - PostgreSQL doesn't support multirecord set. With one trick you can returns set of returned cursors.

RETURN QUERY send result to output, but there are no clean border between results of two RETURN QUERY usage. It is same as UNION.

RETURN QUERY SELECT 1;
RETURN QUERY SELECT 1;
RETURN;

is same as:

RETURN QUERY SELECT 1 UNION SELECT 2;
RETURN;

answered Jan 17, 2014 at 15:21

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

Postgres equivalent to MS-SQL SELECT DataSet needed (returning multiple tables in one query)

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