A question about PL/pgSQL DECLAREd variable behavior

On Thu, Oct 22, 2015 at 2:48 PM, Dane Foster <studdugie@gmail.com> wrote:

I wrote the following simple function to try to learn what happens to a
DECLAREd variable whose assignment comes from an INTO statement where the
query being executed does not return a result.

CREATE OR REPLACE FUNCTION _test() RETURNS BOOLEAN AS $$
DECLARE r RECORD;
BEGIN SELECT 1 AS one INTO r WHERE false; RETURN r IS NULL; END;
$$ LANGUAGE plpgsql;

The function returns true. Given that I can't find any explicit reference in
the documentation about the behavior I've just described is it safe to
assume that the current behavior is the expected behavior but it's just not
documented?

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

"If STRICT is not specified in the INTO clause, then target will be
set to the first row returned by the query, or to nulls if the query
returned no rows."

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Oct 21, 2015 at 10:23 PM, Thomas Munro <
thomas.munro@enterprisedb.com> wrote:

On Thu, Oct 22, 2015 at 2:48 PM, Dane Foster <studdugie@gmail.com> wrote:

I wrote the following simple function to try to learn what happens to a
DECLAREd variable whose assignment comes from an INTO statement where the
query being executed does not return a result.

CREATE OR REPLACE FUNCTION _test() RETURNS BOOLEAN AS $$
DECLARE r RECORD;
BEGIN SELECT 1 AS one INTO r WHERE false; RETURN r IS NULL; END;
$$ LANGUAGE plpgsql;

The function returns true. Given that I can't find any explicit

reference in

the documentation about the behavior I've just described is it safe to
assume that the current behavior is the expected behavior but it's just

not

documented?

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

"If STRICT is not specified in the INTO clause, then target will be
set to the first row returned by the query, or to nulls if the query
returned no rows."

--
Thomas Munro
http://www.enterprisedb.com

​Foot removed from mouth.

Dane​

On 10/21/15 9:32 PM, Dane Foster wrote:

"If STRICT is not specified in the INTO clause, then target will be
set to the first row returned by the query, or to nulls if the query
returned no rows."

​Foot removed from mouth.

Note however that there's some unexpected things when checking whether a
record variable IS (NOT) NULL. It's not as simple as 'has the variable
been set or not'.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

On 10/21/15 9:32 PM, Dane Foster wrote:

"If STRICT is not specified in the INTO clause, then target will be
set to the first row returned by the query, or to nulls if the query
returned no rows."

​Foot removed from mouth.

Note however that there's some unexpected things when checking whether a
record variable IS (NOT) NULL. It's not as simple as 'has the variable been
set or not'.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

​​Please elaborate. I'm entirely new to PL/pgSQL so the more details you
can provide the better.​
​
Thanks,

Dane
​

On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster <studdugie@gmail.com> wrote:

On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

On 10/21/15 9:32 PM, Dane Foster wrote:

"If STRICT is not specified in the INTO clause, then target will be
set to the first row returned by the query, or to nulls if the query
returned no rows."

Foot removed from mouth.

Note however that there's some unexpected things when checking whether a
record variable IS (NOT) NULL. It's not as simple as 'has the variable been
set or not'.

Please elaborate. I'm entirely new to PL/pgSQL so the more details you can
provide the better.
Thanks,

The surprising thing here, required by the standard, is that this
expression is true:

ROW(NULL, NULL) IS NULL

So "r IS NULL" is not a totally reliable way to check if your row
variable was set or not by the SELECT INTO, if there is any chance
that r is a record full of NULL. "r IS NOT DISTINCT FROM NULL" would
work though, because it's only IS [NOT] NULL that has that strange
special case. Other constructs that have special behaviour for NULL
don't consider a composite type composed of NULLs to be NULL. For
example IS DISTINCT FROM, COALESCE, COUNT, STRICT functions.

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro <thomas.munro@enterprisedb.com

wrote:

On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster <studdugie@gmail.com> wrote:

On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <Jim.Nasby@bluetreble.com>

wrote:

On 10/21/15 9:32 PM, Dane Foster wrote:

"If STRICT is not specified in the INTO clause, then target will be
set to the first row returned by the query, or to nulls if the

query

returned no rows."

Foot removed from mouth.

Note however that there's some unexpected things when checking whether a
record variable IS (NOT) NULL. It's not as simple as 'has the variable

been

set or not'.

Please elaborate. I'm entirely new to PL/pgSQL so the more details you

can

provide the better.
Thanks,

The surprising thing here, required by the standard, is that this
expression is true:

ROW(NULL, NULL) IS NULL

So "r IS NULL" is not a totally reliable way to check if your row
variable was set or not by the SELECT INTO, if there is any chance
that r is a record full of NULL. "r IS NOT DISTINCT FROM NULL" would
work though, because it's only IS [NOT] NULL that has that strange
special case. Other constructs that have special behaviour for NULL
don't consider a composite type composed of NULLs to be NULL. For
example IS DISTINCT FROM, COALESCE, COUNT, STRICT functions.

--
Thomas Munro
http://www.enterprisedb.com

​
Someone should include your explanation in the [fine] manual.

Dane​

On Fri, Oct 23, 2015 at 12:33 PM, Dane Foster <studdugie@gmail.com> wrote:

On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster <studdugie@gmail.com> wrote:

On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <Jim.Nasby@bluetreble.com>
wrote:

On 10/21/15 9:32 PM, Dane Foster wrote:

"If STRICT is not specified in the INTO clause, then target will
be
set to the first row returned by the query, or to nulls if the
query
returned no rows."

Foot removed from mouth.

Note however that there's some unexpected things when checking whether
a
record variable IS (NOT) NULL. It's not as simple as 'has the variable
been
set or not'.

Please elaborate. I'm entirely new to PL/pgSQL so the more details you
can
provide the better.
Thanks,

The surprising thing here, required by the standard, is that this
expression is true:

ROW(NULL, NULL) IS NULL

So "r IS NULL" is not a totally reliable way to check if your row
variable was set or not by the SELECT INTO, if there is any chance
that r is a record full of NULL. "r IS NOT DISTINCT FROM NULL" would
work though, because it's only IS [NOT] NULL that has that strange
special case. Other constructs that have special behaviour for NULL
don't consider a composite type composed of NULLs to be NULL. For
example IS DISTINCT FROM, COALESCE, COUNT, STRICT functions.

Someone should include your explanation in the [fine] manual.

The quirky standard behaviour of IS [NOT] NULL with rows is described
in a 'Note' section here:

http://www.postgresql.org/docs/9.4/static/functions-comparison.html

But I do think we should consider pointing out explicitly that "IS
NULL" doesn't mean the same thing as, erm, "is null" where it appears
throughout the documentation, and I proposed a minor tweak:

/messages/by-id/CAEepm=1wW4MGBS6Hwteu6B-OMZiX6_FM=Wfyn7oTeHyCfkgDDw@mail.gmail.com

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Oct 22, 2015 at 9:15 PM, Thomas Munro <thomas.munro@enterprisedb.com

wrote:

On Fri, Oct 23, 2015 at 12:33 PM, Dane Foster <studdugie@gmail.com> wrote:

On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster <studdugie@gmail.com>

wrote:

On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <Jim.Nasby@bluetreble.com>
wrote:

On 10/21/15 9:32 PM, Dane Foster wrote:

"If STRICT is not specified in the INTO clause, then target will
be
set to the first row returned by the query, or to nulls if the
query
returned no rows."

Foot removed from mouth.

Note however that there's some unexpected things when checking

whether

a
record variable IS (NOT) NULL. It's not as simple as 'has the

variable

been
set or not'.

Please elaborate. I'm entirely new to PL/pgSQL so the more details you
can
provide the better.
Thanks,

The surprising thing here, required by the standard, is that this
expression is true:

ROW(NULL, NULL) IS NULL

So "r IS NULL" is not a totally reliable way to check if your row
variable was set or not by the SELECT INTO, if there is any chance
that r is a record full of NULL. "r IS NOT DISTINCT FROM NULL" would
work though, because it's only IS [NOT] NULL that has that strange
special case. Other constructs that have special behaviour for NULL
don't consider a composite type composed of NULLs to be NULL. For
example IS DISTINCT FROM, COALESCE, COUNT, STRICT functions.

Someone should include your explanation in the [fine] manual.

The quirky standard behaviour of IS [NOT] NULL with rows is described
in a 'Note' section here:

http://www.postgresql.org/docs/9.4/static/functions-comparison.html

But I do think we should consider pointing out explicitly that "IS
NULL" doesn't mean the same thing as, erm, "is null" where it appears
throughout the documentation, and I proposed a minor tweak:

/messages/by-id/CAEepm=1wW4MGBS6Hwteu6B-OMZiX6_FM=Wfyn7oTeHyCfkgDDw@mail.gmail.com

--
Thomas Munro
http://www.enterprisedb.com

​
It just occurred to me that another option, for my specific example, would
be to record/cache FOUND instead of testing the RECORD variable for its
NULLness. Unless of course assigning FOUND to a variable is a
pass-by-reference assignment, which in the actual code that I'm writing
would be problematic because FOUND is set many times because there are at
least 4 SQL commands that my function executes.

Dane
​

On 10/22/15 8:52 PM, Dane Foster wrote:

It just occurred to me that another option, for my specific example,
would be to record/cache FOUND instead of testing the RECORD variable
for its NULLness. Unless of course assigning FOUND to a variable is a
pass-by-reference assignment, which in the actual code that I'm writing
would be problematic because FOUND is set many times because there are
at least 4 SQL commands that my function executes.

AFAIK, functions don't really do pass by reference, except for INOUT
parameters. In any case, FOUND is definitely reset when you do things
like SELECT INTO.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2015-10-23 18:05 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 10/22/15 8:52 PM, Dane Foster wrote:

It just occurred to me that another option, for my specific example,
would be to record/cache FOUND instead of testing the RECORD variable
for its NULLness. Unless of course assigning FOUND to a variable is a
pass-by-reference assignment, which in the actual code that I'm writing
would be problematic because FOUND is set many times because there are
at least 4 SQL commands that my function executes.

AFAIK, functions don't really do pass by reference, except for INOUT
parameters. In any case, FOUND is definitely reset when you do things like
SELECT INTO.

Anytime parameters are passed by value. OUT variables are emulated via
dynamic composite types. After execution of any SQL statement is FOUND
variable refreshed.

Regards

Pavel