Re: how to return results from code block

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(at)hot(dot)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.
>