CODEWITHSUNDEEP
javasqlhibernatepostgresql
user2953119
user2953119

Reputation:

How to return a result from an anonymous PL/pgSQL block?

PostgreSQL version I'm using is 9.3.

I've written a code which contains the following chunk:

String queryString = "do $$\n"
+ "<< outerblock >>\n" 
+ "DECLARE\n"
    + "table_name varchar DEFAULT 'partner.partner_statistic';\n"
+ "BEGIN\n"
+    "EXECUTE 'SELECT * FROM ' || table_name;\n"
+ "END;\n"
+ "$$ LANGUAGE plpgsql; \n";
Query query = getSession().createSQLQuery(queryString);
List l = query.list();

In the debugger, I recieve the message:

[org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:78)] : No results were returned by the query.

at the line 

List l = criteria.list();

How can I rewrite the query to return actual result?

Upvotes: 5

Views: 5078

Answers (2)

user2953119
user2953119

Reputation:

The solution I was looking for is that:

CREATE FUNCTION res() RETURNS SETOF partner.partner_statistic AS $$
<< outerblock >>
DECLARE
    r partner.partner_statistic%rowtype;
    table_name varchar DEFAULT 'partner.partner_statistic';
BEGIN
    FOR r IN 
    SELECT * FROM partner.partner_statistic offset 0 limit 100
    LOOP
    RETURN NEXT r;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM res();

Relevant documentation reference is here.

UPDATE: As @a_horse_with_no_name said in comment, there was more simple solution to do that:

CREATE FUNCTION res() RETURNS SETOF partner.partner_statistic AS $$
<< outerblock >>
DECLARE
    r partner.partner_statistic%rowtype;
    table_name varchar DEFAULT 'partner.partner_statistic';
BEGIN
    RETURN QUERY SELECT * FROM partner.partner_statistic offset 0 limit 100
END;
$$ LANGUAGE plpgsql;

SELECT * FROM res();

Upvotes: 1

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61546

Per documentation, a DO statement can't return anything:

The code block is treated as though it were the body of a function with no parameters, returning void

If the code inside the DO is meant to generate rows, you may put them into a temporary table and have the client SELECT from that table.

Upvotes: 9

Related Questions