1

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?

1

This is only an educated guess.

It seems that the SELECT is unnecessary because it's already present, e.g. in the implicit SELECT ... INTO that an assignment := is equivalent to. From the documentation:

An assignment of a value to a PL/pgSQL variable is written as:

variable { := | = } expression;

As explained previously, the expression in such a statement is evaluated by means of an SQL SELECT command sent to the main database engine.

An example may be shown here, where:

name := (SELECT t.name from test_table t where t.id = x);

can also be written as

name := t.name from test_table t where t.id = x;

This seems possible because the second simplified form is equivalent to a SELECT INTO in PL/pgSQL:

SELECT test_table.name INTO name FROM test_table WHERE id = x;

The first form would be equivalent to:

SELECT (SELECT t.name from test_table t where t.id = x) INTO name;

, which has a redundant layer of SELECT that can be removed.

On the other hand, when an implicit SELECT is not present, an explicit SELECT seems necessary. For example, the same function cannot be used directly (i.e. without SELECT)

string_to_array(my_str,'_');

if it's not in an assignment to a variable.

  • some_var := string_to_array(my_str,'_'); will absolutely work (assuming some_var is defined as an array) – a_horse_with_no_name Mar 15 '18 at 16:51
  • @a_horse_with_no_name Yes, my point is that it works only because there is an SELECT implicit in :=. So there is still a SELECT involved in a sense. If string_to_array were a void function, then calling it directly without the assignment part won't work. – tinlyx Mar 15 '18 at 17:00
0

Short answer every expression is a select statement.

https://www.postgresql.org/docs/10/static/plpgsql-expressions.html

All expressions used in PL/pgSQL statements are processed using the server's main SQL executor.

That means you can have from clauses and the like in assignments and after if statements.

IF count(*) > 10  FROM foo_table GROUP BY ORDER BY count(*) DESC LIMIT 1 
THEN
   -- handle the case when there exists a y group larger than 10 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.