Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to return results from code block

how to return results from code block

From
"Andrus"
Date:
How to return single row or results from code block executed using ADO.NET ExecuteQuery() method.
I tried
 
DO $$
declare
  i integer :=0;
 
begin
select i+1 as res1, i+2 as res2;
END$$;
 
but got error:
 
ERROR:  query has no destination for result data
 
How to return single row result from code pgsql  code block ?
 
Andrus.

Re: how to return results from code block

From
Pavel Stehule
Date:
Hello

2012/6/30 Andrus <kobruleht2@hot.ee>:
> How to return single row or results from code block executed using ADO.NET
> ExecuteQuery() method.
> I tried
>
> DO $$
> declare
>   i integer :=0;
>
> begin
> select i+1 as res1, i+2 as res2;
> END$$;
>
> but got error:
>
> ERROR:  query has no destination for result data
>
> How to return single row result from code pgsql  code block ?

you cannot return any result from block - block is similar to void function

Regards

Pavel

>
> Andrus.

Re: how to return results from code block

From
Adrian Klaver
Date:
On 06/30/2012 03:17 AM, Andrus wrote:
> How to return single row or results from code block executed using
> ADO.NET ExecuteQuery() method.
> I tried
> DO $$
> declare
>    i integer :=0;
> begin
> select i+1 as res1, i+2 as res2;
> END$$;
> but got error:
> ERROR: query has no destination for result data
> How to return single row result from code pgsql  code block ?
> Andrus.

Besides what Pavel said about not returning a result there is another
issue with the above. It did not specify a language. I cleaned the
function up a bit:

DO $$
declare
   i integer :=0;
   rec record;

begin
select  i+1 as res1, i+2 as res2 into rec;
raise notice 'The results are %s,%s', rec.res1,rec.res2;
END$$ LANGUAGE plpgsql;

This does not actually return anything but does raise a notice so you
see something happened.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: how to return results from code block

From
Adrian Klaver
Date:
On 06/30/2012 03:17 AM, Andrus wrote:
> How to return single row or results from code block executed using
> ADO.NET ExecuteQuery() method.
> I tried
> DO $$
> declare
>    i integer :=0;
> begin
> select i+1 as res1, i+2 as res2;
> END$$;
> but got error:
> ERROR: query has no destination for result data
> How to return single row result from code pgsql  code block ?
> Andrus.

Oops that should have been:

raise notice 'The results are %,%', rec.res1,rec.res2



--
Adrian Klaver
adrian.klaver@gmail.com

Re: how to return results from code block

From
David Johnston
Date:
On Jun 30, 2012, at 10:18, Adrian Klaver <adrian.klaver@gmail.com> wrote:

Besides what Pavel said about not returning a result there is another issue with the above. It did not specify a language. I cleaned the function up a bit:


The lack of a language is not a problem since the command definition makes it explicitly optional and defaulting to PLPGSQL.

David J.

Re: how to return results from code block

From
Adrian Klaver
Date:
On 06/30/2012 07:37 AM, David Johnston wrote:
> On Jun 30, 2012, at 10:18, Adrian Klaver <adrian.klaver@gmail.com
> <mailto:adrian.klaver@gmail.com>> wrote:
>>
>> Besides what Pavel said about not returning a result there is another
>> issue with the above. It did not specify a language. I cleaned the
>> function up a bit:
>>
>
> The lack of a language is not a problem since the command definition
> makes it explicitly optional and defaulting to PLPGSQL.

Hmm, never got that far in that doc page. Thanks.

>
> David J.


--
Adrian Klaver
adrian.klaver@gmail.com

Re: how to return results from code block

From
Misa Simic
Date:
Hi Andrus,

What is the main goal? Actually what is the reason that you need to execute, and get result in ADO.NET from code block? How I see code block feature, it is more to make easier dev & test of an procedural language function inside code block... Then when we are happy with result, actually create the function with body of your code block... Befor we needed actually to create function... then test... something wrong...i.e. we need one more parameter in function, delete existing function, create new function....test function... something wrong... make changes in function recreate it again... run it.... etc etc...

And yes, Code Block cant return any result so for testing purposes we use:

RAISE NOTICE %, somevariable;
i.e.

DO $$
DECLARE
--input parameters part - should be removed from final CREATE function code... fill default values as some test values for final function
  i integer :=0;
 
--standard function variables...
    rec record;
BEGIN
select i+1 as res1, i+2 as res2 INTO rec;
RAISE NOTICE %, rec;
END$$;

OK, if we are happy with result, we would copy/paste that code inside CREATE Function, remove "Input Parameters part" in DECLARE section and actually set them as function Input parameters... Replace on the end "RAISE NOTICE %, rec;" with "return rec" (if that is actually what function should return...) and job done... much easier than: execute Create, execute SELECT function, change code... execute CREATE, execute SELECT... etc etc... 


To get result in any client language, we must execute just SELECT query...

So basically you have several options:

1) make plpgsql function with input parameter "i" and body as your code in code block.... And execute "SELECT * FROM myfunctionname(0)" in .Net (better option)

2) make logic in .Net to actually create final Query based on your input parametar... i.e.

text getSQL(int t) 
{
string sql = "select " + i.ToString() + " +1 as res1, " + i.ToString+ "+2 as res2;";
return sql;
}

And execute that query with ExecuteQuery method...

Kind Regards,

Misa

2012/6/30 Andrus <kobruleht2@hot.ee>
How to return single row or results from code block executed using ADO.NET ExecuteQuery() method.
I tried
 
DO $$
declare
  i integer :=0;
 
begin
select i+1 as res1, i+2 as res2;
END$$;
 
but got error:
 
ERROR:  query has no destination for result data
 
How to return single row result from code pgsql  code block ?
 
Andrus.