pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tobias Gierke <tobias.gierke@code-sourcery.de>
To: pgsql-bugs@lists.postgresql.org
Subject: Assignment to composite type variable fails inside function but running query separately yields correct type & value ?
Date: Tue, 26 Jan 2021 13:56:44 +0100
Message-ID: <4446d7d8-08a3-03d6-3f46-ac80239dbfb9@code-sourcery.de> (raw)

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








view thread (3+ messages)  latest in thread

Message-ID: <4446d7d8-08a3-03d6-3f46-ac80239dbfb9@code-sourcery.de>
Permalink:  ../4446d7d8-08a3-03d6-3f46-ac80239dbfb9@code-sourcery.de/
Also on:    postgresql.org/message-id/4446d7d8-08a3-03d6-3f46-ac80239dbfb9@code-sourcery.de

 · 

reply

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: tobias.gierke@code-sourcery.de, pgsql-bugs@lists.postgresql.org
  Subject: Re: Assignment to composite type variable fails inside function but running query separately yields correct type & value ?
  In-Reply-To: <4446d7d8-08a3-03d6-3f46-ac80239dbfb9@code-sourcery.de>

* 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