On 10/06/2015 01:48 PM, Oleksii Kliukin wrote:
On 06 Oct 2015, at 22:40, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
On 10/06/2015 01:13 PM, Oleksii Kliukin wrote:
Basically, if we invoke the first example, the foo table with have only
1 row and not 10, as supplied by the generate_series.
However, when ORDER BY is attached to the query, or aggregate (such as
max, min or array_agg) is wrapped around the test(id) call, the test
function is called exactly 10 times. If I replace the SELECT INTO with
PERFORM, it would also be called 10 times. Unfortunately, it is not
possible to use PERFORM directly in the CTE _expression_.
What CTE _expression_?
Any CTE _expression_ :-). The example here is just an illustration to
expose the issue. The real-world query I came across used a complex CTE
_expression_ and called a function at the end of it inside the SELECT INTO
statement.
Remember SELECT INTO inside plpgsql is different from SELECT INTO outside:http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW"Tip: Note that this interpretation of SELECT with INTO is quite different from PostgreSQL's regular SELECT INTO command, wherein the INTO target is a newly created table. If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE ... AS SELECT.
Thank you. In this case SELECT INTO was consciously called inside the pl/pgSQL function, as one cannot do SELECT function() from pl/pgSQL without storing the result of the function somewhere (with the INTO clause).
Kind regards
|
