Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l4Nts-0007SY-6W for pgsql-bugs@arkaria.postgresql.org; Tue, 26 Jan 2021 12:56:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l4Ntq-0008NK-50 for pgsql-bugs@arkaria.postgresql.org; Tue, 26 Jan 2021 12:56:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l4Ntp-0008ND-V4 for pgsql-bugs@lists.postgresql.org; Tue, 26 Jan 2021 12:56:49 +0000 Received: from mail2.code-sourcery.de ([138.201.66.53]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l4Ntn-0005On-Go for pgsql-bugs@lists.postgresql.org; Tue, 26 Jan 2021 12:56:49 +0000 Received: from [192.168.188.22] (dynamic-095-112-026-239.95.112.pool.telefonica.de [95.112.26.239]) by mail2.code-sourcery.de (Postfix) with ESMTPSA id A67201FC76 for ; Tue, 26 Jan 2021 13:56:45 +0100 (CET) To: pgsql-bugs@lists.postgresql.org From: Tobias Gierke Subject: Assignment to composite type variable fails inside function but running query separately yields correct type & value ? Message-ID: <4446d7d8-08a3-03d6-3f46-ac80239dbfb9@code-sourcery.de> Date: Tue, 26 Jan 2021 13:56:44 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.6.1 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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