pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedAssignment to composite type variable fails inside function but running query separately yields correct type & value ?
3+ messages / 2 participants
[nested] [flat]
* Assignment to composite type variable fails inside function but running query separately yields correct type & value ?
@ 2021-01-26 12:56 Tobias Gierke <tobias.gierke@code-sourcery.de>
2021-01-26 15:08 ` Re: Assignment to composite type variable fails inside function but running query separately yields correct type & value ? David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tobias Gierke @ 2021-01-26 12:56 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hi,
I'm probably just missing some nitty-gritty detail here but I've bashed
by head against this one for ages and still couldn't make sense of it.
This is on PostgreSQL 12.2 (on CentOS 7 but PostgreSQL compiled from
sources):
voip=# CREATE TYPE version_num AS (major bigint, minor bigint);
CREATE TYPE
voip=# CREATE TYPE version_num_text AS (major text, minor text);
CREATE TYPE
voip=#
voip=# CREATE TEMPORARY TABLE test_table ( a_major bigint, a_minor bigint );
CREATE TABLE
voip=# INSERT INTO test_table VALUES (1,1);
INSERT 0 1
voip=#
voip=# CREATE OR REPLACE FUNCTION test_func( _id version_num_text )
voip-# RETURNS void AS
voip-# $BODY$
voip$# DECLARE
voip$# _myid version_num;
voip$# BEGIN
voip$# SELECT (t1.a_major, t1.a_minor )::version_num INTO _myid
FROM test_table t1 WHERE (t1.a_major,t1.a_minor)::version_num_text = _id;
voip$# END;
voip$# $BODY$ LANGUAGE plpgsql;
CREATE FUNCTION
voip=#
voip=# SELECT test_func( ('1','1')::version_num_text );
ERROR: invalid input syntax for type bigint: "(1,1)"
CONTEXT: PL/pgSQL function test_func(version_num_text) line 5 at SQL
statement
voip=# CREATE TEMPORARY TABLE dbg_table AS SELECT (t1.a_major,
t1.a_minor )::version_num FROM test_table t1 WHERE
(t1.a_major,t1.a_minor)::version_num_text = ('1','1')::version_num_text;
SELECT 1
voip=# \d dbg_table
Table "pg_temp_189.dbg_table"
Column | Type | Collation | Nullable | Default
--------+-------------+-----------+----------+---------
row | version_num | | |
Bug or feature ?
Cheers,
Tobias
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Assignment to composite type variable fails inside function but running query separately yields correct type & value ?
2021-01-26 12:56 Assignment to composite type variable fails inside function but running query separately yields correct type & value ? Tobias Gierke <tobias.gierke@code-sourcery.de>
@ 2021-01-26 15:08 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-26 15:19 ` Re: Assignment to composite type variable fails inside function but running query separately yields correct type & value ? Tobias Gierke <tobias.gierke@code-sourcery.de>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2021-01-26 15:08 UTC (permalink / raw)
To: Tobias Gierke <tobias.gierke@code-sourcery.de>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Jan 26, 2021 at 5:56 AM Tobias Gierke <
tobias.gierke@code-sourcery.de> wrote:
> voip$# SELECT (t1.a_major, t1.a_minor )::version_num INTO _myid
>
> Bug or feature ?
>
>
Feature. The first column of the select result gets placed into the first
field of _myid, the second column of the select result gets placed into the
second field of _myid. This is how assignment to row-typed variables (and
record-typed too) works in pl/pgsql.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Assignment to composite type variable fails inside function but running query separately yields correct type & value ?
2021-01-26 12:56 Assignment to composite type variable fails inside function but running query separately yields correct type & value ? Tobias Gierke <tobias.gierke@code-sourcery.de>
2021-01-26 15:08 ` Re: Assignment to composite type variable fails inside function but running query separately yields correct type & value ? David G. Johnston <david.g.johnston@gmail.com>
@ 2021-01-26 15:19 ` Tobias Gierke <tobias.gierke@code-sourcery.de>
0 siblings, 0 replies; 3+ messages in thread
From: Tobias Gierke @ 2021-01-26 15:19 UTC (permalink / raw)
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Thanks for the hint & sorry for the noise, it's working now !
> On Tue, Jan 26, 2021 at 5:56 AM Tobias Gierke
> <tobias.gierke@code-sourcery.de
> <mailto:tobias.gierke@code-sourcery.de>> wrote:
>
> voip$# SELECT (t1.a_major, t1.a_minor )::version_num INTO
> _myid
>
> Bug or feature ?
>
>
> Feature. The first column of the select result gets placed into the
> first field of _myid, the second column of the select result gets
> placed into the second field of _myid. This is how assignment to
> row-typed variables (and record-typed too) works in pl/pgsql.
>
> David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-01-26 15:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-26 12:56 Assignment to composite type variable fails inside function but running query separately yields correct type & value ? Tobias Gierke <tobias.gierke@code-sourcery.de>
2021-01-26 15:08 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-26 15:19 ` Tobias Gierke <tobias.gierke@code-sourcery.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