Thread: ERROR: query has no destination for result data

Hello,

I wish to return the SELECT statement.

Ho can I achieve this?

-- 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

-- 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

>
>
> 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
>> Bartek
>>
>> Pozdrawiam,
>> Bartek
>>
>>
>>
>> 2012/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


>
>
> 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
>> >> Bartek
>> >>
>> >> Pozdrawiam,
>> >> Bartek
>> >>
>> >>
>> >>
>> >> 2012/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

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 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.


>
> Regards
> Gavan
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com