ERROR: query has no destination for result data

[Peter Kroon]

Hello,

I wish to return the SELECT statement.
Ho can I achieve this?

DO $$

DECLARE v_some_id int=14;

BEGIN
/*
more queries here...
*/
SELECT 'this is text';
END
$$ LANGUAGE plpgsql;

Best,
Peter Kroon

[Bartosz Dmytrak]

Hi,
according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html
DO returns void:
*"The code block is treated as though it were the body of a function with
no parameters, returning void."*
*
*
Regars
Bartek

Pozdrawiam,
Bartek

Post by Peter Kroon
Hello,
I wish to return the SELECT statement.
Ho can I achieve this?
DO $$
DECLARE v_some_id int=14;
BEGIN
/*
more queries here...
*/
SELECT 'this is text';
END
$$ LANGUAGE plpgsql;
Best,
Peter Kroon

[Peter Kroon]

So this means it's unable to return data?

Post by Bartosz Dmytrak
Hi,
according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html
*"The code block is treated as though it were the body of a function with
no parameters, returning void."*
*
*
Regars
Bartek
Pozdrawiam,
Bartek

Post by Peter Kroon
Hello,
I wish to return the SELECT statement.
Ho can I achieve this?
DO $$
DECLARE v_some_id int=14;
BEGIN
/*
more queries here...
*/
SELECT 'this is text';
END
$$ LANGUAGE plpgsql;
Best,
Peter Kroon

[Pavel Stehule]

Post by Peter Kroon
So this means it's unable to return data?

yes, it means :(

DO "is" void function, so you cannot to return anything

Regards

Pavel Stehule

Post by Peter Kroon

Post by Bartosz Dmytrak
Hi,
according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html
"The code block is treated as though it were the body of a function with
no parameters, returning void."
Regars
Bartek
Pozdrawiam,
Bartek

Post by Peter Kroon
Hello,
I wish to return the SELECT statement.
Ho can I achieve this?
DO $$
DECLARE v_some_id int=14;
BEGIN
/*
more queries here...
*/
SELECT 'this is text';
END
$$ LANGUAGE plpgsql;
Best,
Peter Kroon

[Pavel Stehule]

OK, but how do I run some SQL in pgAdmin with declared variables?

pgAdmin has own client language similar to T-SQL
http://www.pgadmin.org/docs/dev/pgscript.html - but it is client side
language

if you like server side code, then you have to write table function.

Regards

Pavel

Post by Pavel Stehule

Post by Peter Kroon
So this means it's unable to return data?

yes, it means :(
DO "is" void function, so you cannot to return anything
Regards
Pavel Stehule

Post by Peter Kroon

Post by Bartosz Dmytrak
Hi,
according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html
"The code block is treated as though it were the body of a function with
no parameters, returning void."
Regars
Bartek
Pozdrawiam,
Bartek

Post by Peter Kroon
Hello,
I wish to return the SELECT statement.
Ho can I achieve this?
DO $$
DECLARE v_some_id int=14;
BEGIN
/*
more queries here...
*/
SELECT 'this is text';
END
$$ LANGUAGE plpgsql;
Best,
Peter Kroon

[Peter Kroon]

Thanks, I'll have a look at this.

Post by Pavel Stehule

OK, but how do I run some SQL in pgAdmin with declared variables?

pgAdmin has own client language similar to T-SQL
http://www.pgadmin.org/docs/dev/pgscript.html - but it is client side
language
if you like server side code, then you have to write table function.
Regards
Pavel

Post by Pavel Stehule

Post by Peter Kroon
So this means it's unable to return data?

yes, it means :(
DO "is" void function, so you cannot to return anything
Regards
Pavel Stehule

Post by Peter Kroon

Post by Bartosz Dmytrak
Hi,

http://www.postgresql.org/docs/9.2/static/sql-do.html

Post by Pavel Stehule

Post by Peter Kroon

Post by Bartosz Dmytrak
"The code block is treated as though it were the body of a function with
no parameters, returning void."
Regars
Bartek
Pozdrawiam,
Bartek

Post by Peter Kroon
Hello,
I wish to return the SELECT statement.
Ho can I achieve this?
DO $$
DECLARE v_some_id int=14;
BEGIN
/*
more queries here...
*/
SELECT 'this is text';
END
$$ LANGUAGE plpgsql;
Best,
Peter Kroon

[Peter Kroon]

OK, but how do I run some SQL in pgAdmin with declared variables?

Post by Pavel Stehule

Post by Peter Kroon
So this means it's unable to return data?

yes, it means :(
DO "is" void function, so you cannot to return anything
Regards
Pavel Stehule

Post by Peter Kroon

Post by Bartosz Dmytrak
Hi,
according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html
"The code block is treated as though it were the body of a function with
no parameters, returning void."
Regars
Bartek
Pozdrawiam,
Bartek

Post by Peter Kroon
Hello,
I wish to return the SELECT statement.
Ho can I achieve this?
DO $$
DECLARE v_some_id int=14;
BEGIN
/*
more queries here...
*/
SELECT 'this is text';
END
$$ LANGUAGE plpgsql;
Best,
Peter Kroon

[Peter Kroon]

When using:
RETURN QUERY(
SELECT 'this is text'
);

I get another error:
ERROR: cannot use RETURN QUERY in a non-SETOF function

Post by Peter Kroon
Hello,
I wish to return the SELECT statement.
Ho can I achieve this?
RETURN QUERY.
See
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html<http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html>
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

[Craig Ringer]

Post by Peter Kroon
RETURN QUERY(
SELECT 'this is text'
);
ERROR: cannot use RETURN QUERY in a non-SETOF function

Use a plain SQL function instead of PL/PgSQL, or use `SELECT INTO` and
ordinary `RETURN`.

[Peter Kroon]

When using plain SQL I get this message:
ERROR: language "sql" does not support inline code execution
When removing the BEGIN+END block statements the message persists.

Post by Peter Kroon
RETURN QUERY(
SELECT 'this is text'
);
ERROR: cannot use RETURN QUERY in a non-SETOF function
Use a plain SQL function instead of PL/PgSQL, or use `SELECT INTO` and
ordinary `RETURN`.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

[John R Pierce]

Post by Peter Kroon
ERROR: cannot use RETURN QUERY in a non-SETOF function

what is your function deined to return?

a query returns a set of records, even if that set is 1 record of 1
field (like, select 'some text';)

you could declare a record variable, and use SELECT ... INTO myrecordvar
[FROM ...];
http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
then return a field of that record variable.

see this example...
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
... for how you would loop through query results
--
john r pierce N 37, W 122
santa cruz ca mid-left coast

[Peter Kroon]

Yes, but this means I have to create a function which is something I don't
want.
I just want to debug some of my code in the related function.
So what I want is in pgAdmin declare some vars and run the sql and get the
result.

Post by John R Pierce

Post by Peter Kroon
ERROR: cannot use RETURN QUERY in a non-SETOF function

what is your function deined to return?
a query returns a set of records, even if that set is 1 record of 1 field
(like, select 'some text';)
you could declare a record variable, and use SELECT ... INTO myrecordvar
[FROM ...];
http://www.postgresql.org/**docs/current/static/plpgsql-**
statements.html#PLPGSQL-**STATEMENTS-SQL-ONEROW<http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW>
then return a field of that record variable.
see this example...
http://www.postgresql.org/**docs/current/static/plpgsql-**
control-structures.html#**PLPGSQL-RECORDS-ITERATING<http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING>
... for how you would loop through query results
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
--
http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>

[Adrian Klaver]

Post by Peter Kroon
Yes, but this means I have to create a function which is something I
don't want.
I just want to debug some of my code in the related function.
So what I want is in pgAdmin declare some vars and run the sql and get
the result.

The way I handle this is to use RAISE NOTICE in place of RETURN.

[Craig Ringer]

Post by Peter Kroon
Hello,
I wish to return the SELECT statement.
Ho can I achieve this?

RETURN QUERY.

See
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html
<http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html>

[Gavan Schneider]

Post by Peter Kroon
Hello,
I wish to return the SELECT statement.
Ho can I achieve this?

DO $$

DECLARE v_some_id int=14;

BEGIN
/*
more queries here...
*/
SELECT 'this is text';
END
$$ LANGUAGE plpgsql;

Post by Peter Kroon
Best,
Peter Kroon

Reinterpreting the question and taking the pseudocode
semi-literally is
the following closer to what was asked?
ref. 39.2. Structure of PL/pgSQL
<http://www.postgresql.org/docs/9.2/static/plpgsql-structure.html>

pendari=> CREATE FUNCTION somefunc() RETURNS text AS $$
pendari$>
pendari$> DECLARE
pendari$> v_some_id int=14;
pendari$> BEGIN
pendari$> /*
pendari$> more queries here...
pendari$> */
pendari$> RETURN 'this is text'::text;
pendari$> END;
pendari$> $$ LANGUAGE plpgsql;
CREATE FUNCTION
pendari=> select somefunc();
somefunc
--------------
this is text
(1 row)

pendari=>

Regards
Gavan

[Adrian Klaver]

Post by Peter Kroon

Post by Peter Kroon
Hello,
I wish to return the SELECT statement.
Ho can I achieve this?

DO $$
DECLARE v_some_id int=14;
BEGIN
/*
more queries here...
*/
SELECT 'this is text';
END
$$ LANGUAGE plpgsql;

Post by Peter Kroon
Best,
Peter Kroon

Reinterpreting the question and taking the pseudocode semi-literally is
the following closer to what was asked?

The rub is that the OP wants to do this in a DO block which rules out
using RETURN.

Post by Peter Kroon
Regards
Gavan

[Peter Kroon]

Post by Peter Kroon
Reinterpreting the question and taking the pseudocode semi-literally is
the following closer to what was asked?

No, I don't want to use/create a function.

Best,
Peter

Post by Peter Kroon
Hello,

Post by Peter Kroon
I wish to return the SELECT statement.
Ho can I achieve this?
DO $$

DECLARE v_some_id int=14;
BEGIN
/*
more queries here...
*/
SELECT 'this is text';
END
$$ LANGUAGE plpgsql;

Post by Peter Kroon
Best,
Peter Kroon
Reinterpreting the question and taking the pseudocode semi-literally is

the following closer to what was asked?
ref. 39.2. Structure of PL/pgSQL
<http://www.postgresql.org/**docs/9.2/static/plpgsql-**
structure.html<http://www.postgresql.org/docs/9.2/static/plpgsql-structure.html>
pendari=> CREATE FUNCTION somefunc() RETURNS text AS $$
pendari$>
pendari$> DECLARE
pendari$> v_some_id int=14;
pendari$> BEGIN
pendari$> /*
pendari$> more queries here...
pendari$> */
pendari$> RETURN 'this is text'::text;
pendari$> END;
pendari$> $$ LANGUAGE plpgsql;
CREATE FUNCTION
pendari=> select somefunc();
somefunc
--------------
this is text
(1 row)
pendari=>
Regards
Gavan
--
http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>