While assigning the result of a function or expression to a variable in PL/pgSQL, when is it necessary to use a SELECT
statement (sub-query)?
This may be just my misconception from SQL. I thought that everything should be enclosed in a (SELECT ...)
(even if it just involves computing a number), e.g.
$$
DECLARE parts text[];
BEGIN
parts := (SELECT string_to_array(my_str,'_') );
...
$$
but I just found that it works without the SELECT
:
parts := string_to_array(my_str,'_');
(It's almost as if I can use PL/pgSQL as if its Pascal.)
In general, what kind of expressions can be used directly without SELECT
in computing values?
Best Answer
This is only an educated guess.
It seems that the
SELECT
is unnecessary because it's already present, e.g. in the implicitSELECT ... INTO
that an assignment:=
is equivalent to. From the documentation:An example may be shown here, where:
can also be written as
This seems possible because the second simplified form is equivalent to a
SELECT INTO
in PL/pgSQL:The first form would be equivalent to:
, which has a redundant layer of
SELECT
that can be removed.On the other hand, when an implicit
SELECT
is not present, an explicitSELECT
seems necessary. For example, the same function cannot be used directly (i.e. withoutSELECT
)if it's not in an assignment to a variable.