From: Tom Lane <tgl@sss.pgh.pa.us>
To: jbe-mlist@magnetkern.de
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type
Date: Wed, 06 Nov 2024 17:30:02 -0500
Message-ID: <3305167.1730932202@sss.pgh.pa.us> (raw)
In-Reply-To: <18693-65968418890877b4@postgresql.org>
References: <18693-65968418890877b4@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jbe-mlist@magnetkern.de, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18693: Column names not set when using SELECT STRICT INTO with RECORD type
In-Reply-To: <3305167.1730932202@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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