ERROR: query has no destination for result data
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
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
2012/11/23 Peter Kroon <plakroon@gmail.com>
Show quoted text
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
On 11/23/2012 06:36 PM, Peter Kroon wrote:
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
When using:
RETURN QUERY(
SELECT 'this is text'
);
I get another error:
ERROR: cannot use RETURN QUERY in a non-SETOF function
2012/11/23 Craig Ringer <craig@2ndquadrant.com>
Show quoted text
On 11/23/2012 06:36 PM, Peter Kroon wrote:
Hello,
I wish to return the SELECT statement.
Ho can I achieve this?RETURN QUERY.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
So this means it's unable to return data?
2012/11/23 Bartosz Dmytrak <bdmytrak@gmail.com>
Show quoted text
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
BartekPozdrawiam,
Bartek2012/11/23 Peter Kroon <plakroon@gmail.com>
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
On 11/23/2012 06:53 PM, Peter Kroon wrote:
When using:
RETURN QUERY(
SELECT 'this is text'
);I get another error:
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
2012/11/23 Peter Kroon <plakroon@gmail.com>:
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
Show quoted text
2012/11/23 Bartosz Dmytrak <bdmytrak@gmail.com>
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
BartekPozdrawiam,
Bartek2012/11/23 Peter Kroon <plakroon@gmail.com>
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
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.
2012/11/23 Craig Ringer <craig@2ndquadrant.com>
Show quoted text
On 11/23/2012 06:53 PM, Peter Kroon wrote:
When using:
RETURN QUERY(
SELECT 'this is text'
);I get another error:
ERROR: cannot use RETURN QUERY in a non-SETOF functionUse 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
OK, but how do I run some SQL in pgAdmin with declared variables?
2012/11/23 Pavel Stehule <pavel.stehule@gmail.com>
Show quoted text
2012/11/23 Peter Kroon <plakroon@gmail.com>:
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
2012/11/23 Bartosz Dmytrak <bdmytrak@gmail.com>
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
BartekPozdrawiam,
Bartek2012/11/23 Peter Kroon <plakroon@gmail.com>
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
2012/11/23 Peter Kroon <plakroon@gmail.com>:
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
Show quoted text
2012/11/23 Pavel Stehule <pavel.stehule@gmail.com>
2012/11/23 Peter Kroon <plakroon@gmail.com>:
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
2012/11/23 Bartosz Dmytrak <bdmytrak@gmail.com>
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
BartekPozdrawiam,
Bartek2012/11/23 Peter Kroon <plakroon@gmail.com>
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
On 11/23/12 2:53 AM, Peter Kroon wrote:
I get another error:
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
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.
2012/11/23 John R Pierce <pierce@hogranch.com>
Show quoted text
On 11/23/12 2:53 AM, Peter Kroon wrote:
I get another error:
ERROR: cannot use RETURN QUERY in a non-SETOF functionwhat 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--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
Thanks, I'll have a look at this.
2012/11/23 Pavel Stehule <pavel.stehule@gmail.com>
Show quoted text
2012/11/23 Peter Kroon <plakroon@gmail.com>:
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
languageif you like server side code, then you have to write table function.
Regards
Pavel
2012/11/23 Pavel Stehule <pavel.stehule@gmail.com>
2012/11/23 Peter Kroon <plakroon@gmail.com>:
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
2012/11/23 Bartosz Dmytrak <bdmytrak@gmail.com>
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
BartekPozdrawiam,
Bartek2012/11/23 Peter Kroon <plakroon@gmail.com>
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
On 11/23/2012 03:25 AM, Peter Kroon wrote:
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.
--
Adrian Klaver
adrian.klaver@gmail.com
On Friday, November 23, 2012 at 21:36, Peter Kroon wrote:
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
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
On 11/24/2012 12:46 AM, Gavan Schneider wrote:
On Friday, November 23, 2012 at 21:36, Peter Kroon wrote:
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 KroonReinterpreting 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.
Regards
Gavan
--
Adrian Klaver
adrian.klaver@gmail.com
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
2012/11/24 Gavan Schneider <pg-gts@snkmail.com>
Show quoted text
On Friday, November 23, 2012 at 21:36, Peter Kroon wrote:
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 KroonReinterpreting 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--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>