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@xxxxxx>
How to return single row or results from code block executed using ADO.NET ExecuteQuery() method.I triedDO $$declarei integer :=0;beginselect i+1 as res1, i+2 as res2;END$$;but got error:ERROR: query has no destination for result dataHow to return single row result from code pgsql code block ?Andrus.
