pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type
3+ messages / 3 participants
[nested] [flat]
* BUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type
@ 2024-11-06 18:05 PG Bug reporting form <noreply@postgresql.org>
2024-11-06 22:30 ` Re: BUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2024-11-06 18:05 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jbe-mlist@magnetkern.de
The following bug has been logged on the website:
Bug reference: 18693
Logged by: Jan Behrens
Email address: jbe-mlist@magnetkern.de
PostgreSQL version: 16.4
Operating system: FreeBSD
Description:
I used the following SQL code:
CREATE TABLE tbl (a TEXT, b TEXT);
CREATE FUNCTION "buggy"() RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
t tbl%ROWTYPE;
r RECORD;
BEGIN
SELECT a, b INTO t FROM (SELECT 'A' AS a, 'B' AS b) AS q;
RAISE NOTICE 'non-strict t = %', t;
RAISE NOTICE 'non-strict t.a = %', t.a;
RAISE NOTICE 'non-strict t.b = %', t.b;
SELECT a, b STRICT INTO t FROM (SELECT 'A' AS a, 'B' AS b) AS q;
RAISE NOTICE 'STRICT t = %', t;
RAISE NOTICE 'STRICT t.a = %', t.a;
RAISE NOTICE 'STRICT t.b = %', t.b;
SELECT a, b INTO r FROM (SELECT 'A' AS a, 'B' AS b) AS q;
RAISE NOTICE 'non-strict r = %', r;
RAISE NOTICE 'non-strict r.a = %', r.a;
RAISE NOTICE 'non-strict r.b = %', r.b;
SELECT a, b STRICT INTO r FROM (SELECT 'A' AS a, 'B' AS b) AS q;
RAISE NOTICE 'STRICT r = %', r;
RAISE NOTICE 'STRICT r.a = %', r.a;
RAISE NOTICE 'STRICT r.b = %', r.b;
END;
$$;
SELECT buggy();
Invoking the buggy() function resulted in:
NOTICE: non-strict t = (A,B)
NOTICE: non-strict t.a = A
NOTICE: non-strict t.b = B
NOTICE: STRICT t = (A,B)
NOTICE: STRICT t.a = A
NOTICE: STRICT t.b = B
NOTICE: non-strict r = (A,B)
NOTICE: non-strict r.a = A
NOTICE: non-strict r.b = B
NOTICE: STRICT r = (A,B)
NOTICE: STRICT r.a = A
ERROR: record "r" has no field "b"
CONTEXT: SQL expression "r.b"
PL/pgSQL function buggy() line 21 at RAISE
I would expect the last statement to not result in an error but to correctly
access field "b" as in the non-strict case or tbl%ROWTYPE case.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type
2024-11-06 18:05 BUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type PG Bug reporting form <noreply@postgresql.org>
@ 2024-11-06 22:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-11-07 01:23 ` Re: BUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type Jan Behrens <jbe-mlist@magnetkern.de>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2024-11-06 22:30 UTC (permalink / raw)
To: jbe-mlist@magnetkern.de; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> I used the following SQL code:
> CREATE TABLE tbl (a TEXT, b TEXT);
> CREATE FUNCTION "buggy"() RETURNS VOID LANGUAGE plpgsql AS $$
> DECLARE
> t tbl%ROWTYPE;
> r RECORD;
> BEGIN
> SELECT a, b INTO t FROM (SELECT 'A' AS a, 'B' AS b) AS q;
> RAISE NOTICE 'non-strict t = %', t;
> RAISE NOTICE 'non-strict t.a = %', t.a;
> RAISE NOTICE 'non-strict t.b = %', t.b;
> SELECT a, b STRICT INTO t FROM (SELECT 'A' AS a, 'B' AS b) AS q;
> RAISE NOTICE 'STRICT t = %', t;
> RAISE NOTICE 'STRICT t.a = %', t.a;
> RAISE NOTICE 'STRICT t.b = %', t.b;
> SELECT a, b INTO r FROM (SELECT 'A' AS a, 'B' AS b) AS q;
> RAISE NOTICE 'non-strict r = %', r;
> RAISE NOTICE 'non-strict r.a = %', r.a;
> RAISE NOTICE 'non-strict r.b = %', r.b;
> SELECT a, b STRICT INTO r FROM (SELECT 'A' AS a, 'B' AS b) AS q;
> RAISE NOTICE 'STRICT r = %', r;
> RAISE NOTICE 'STRICT r.a = %', r.a;
> RAISE NOTICE 'STRICT r.b = %', r.b;
> END;
> $$;
You have the syntax wrong: what you intended (I presume) is
SELECT a, b INTO STRICT r FROM (SELECT 'A' AS a, 'B' AS b) AS q;
With what you wrote, the STRICT is taken as an output column alias of
the outer SELECT, as if the command were
SELECT a, b AS "strict" INTO r FROM (SELECT 'A' AS a, 'B' AS b) AS q;
The alias makes no difference when assigning to "t", since its column
names are predetermined. But when assigning to "r", the second column
winds up being named "strict" not "b". You can demonstrate this with
SELECT a, b STRICT INTO r FROM (SELECT 'A' AS a, 'B' AS b) AS q;
RAISE NOTICE 'STRICT r = %', r;
RAISE NOTICE 'STRICT r.a = %', r.a;
RAISE NOTICE 'STRICT r.strict = %', r."strict";
(It's kind of annoying that "strict" has to be double-quoted
in the RAISE NOTICE, especially since you get a rather misleading
error if it isn't. But that seems like a different discussion.)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type
2024-11-06 18:05 BUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type PG Bug reporting form <noreply@postgresql.org>
2024-11-06 22:30 ` Re: BUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-11-07 01:23 ` Jan Behrens <jbe-mlist@magnetkern.de>
0 siblings, 0 replies; 3+ messages in thread
From: Jan Behrens @ 2024-11-07 01:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
On Wed, 06 Nov 2024 17:30:02 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> [...]
>
> You have the syntax wrong: what you intended (I presume) is
>
> SELECT a, b INTO STRICT r FROM (SELECT 'A' AS a, 'B' AS b) AS q;
>
> With what you wrote, the STRICT is taken as an output column alias of
> the outer SELECT, [...]
>
> regards, tom lane
Oooops, my apologies! It totally makes sense now.
Thanks for your thorough/helpful response.
Regards
Jan Behrens
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-11-07 01:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-06 18:05 BUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type PG Bug reporting form <noreply@postgresql.org>
2024-11-06 22:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-11-07 01:23 ` Jan Behrens <jbe-mlist@magnetkern.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox