agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
Cc: pgsql-bugs@lists.postgresql.org
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Weird "could not determine which collation to use for string comparison" with LEAST/GREATEST on PG11 procedure
Date: Tue, 5 Feb 2019 15:11:32 +0100
Message-ID: <66209b1a-db5b-d578-6674-1be789c581f5@2ndquadrant.com> (raw)
In-Reply-To: <12249.1542824360@sss.pgh.pa.us>
References: <1EC8157EB499BF459A516ADCF135ADCE39FFAC54@LON-WGMSX712.ad.moodys.net>
<12249.1542824360@sss.pgh.pa.us>
On 21/11/2018 19:19, Tom Lane wrote:
> "Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com> writes:
>> SIMPLE=> create or replace procedure same_values_proc(a text, b text) as $body$
>> SIMPLE$> begin
>> SIMPLE$> assert a = b;
>> SIMPLE$> end;$body$ language plpgsql;
>> CREATE PROCEDURE
>> SIMPLE=>
>> SIMPLE=> call same_values_proc(least('a','b'),'a');
>> ERROR: could not determine which collation to use for string comparison
>> HINT: Use the COLLATE clause to set the collation explicitly.
>
> Yeah, same here. I think somebody forgot to run assign_expr_collations()
> on CALL arguments.
This appears to fix it.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 5c68d4c057f2ed41d03faf46e20e33df24990305 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Tue, 5 Feb 2019 15:08:53 +0100
Subject: [PATCH] Add collation assignment to CALL statement
Otherwise functions that require collation information will not have
it if they are called in arguments to a CALL statement.
Reported-by: Jean-Marc Voillequin <Jean-Marc.Voillequin@moodys.com>
Discussion: https://www.postgresql.org/message-id/flat/1EC8157EB499BF459A516ADCF135ADCE39FFAC54%40LON-WGMSX712.a...
---
src/backend/parser/analyze.c | 2 ++
src/test/regress/expected/create_procedure.out | 7 +++++++
src/test/regress/sql/create_procedure.sql | 11 +++++++++++
3 files changed, 20 insertions(+)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 7f5773582b..e3544efb6f 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -2636,6 +2636,8 @@ transformCallStmt(ParseState *pstate, CallStmt *stmt)
true,
stmt->funccall->location);
+ assign_expr_collations(pstate, node);
+
stmt->funcexpr = castNode(FuncExpr, node);
result = makeNode(Query);
diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out
index 5b9b83839c..211a42cefa 100644
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -139,6 +139,13 @@ AS $$
SELECT NULL::int;
$$;
CALL ptest6(1, 2);
+-- collation assignment
+CREATE PROCEDURE ptest7(a text, b text)
+LANGUAGE SQL
+AS $$
+SELECT a = b;
+$$;
+CALL ptest7(least('a', 'b'), 'a');
-- various error cases
CALL version(); -- error: not a procedure
ERROR: version() is not a procedure
diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql
index b64293ed66..89b96d580f 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -101,6 +101,17 @@ CREATE PROCEDURE ptest6(a int, b anyelement)
CALL ptest6(1, 2);
+-- collation assignment
+
+CREATE PROCEDURE ptest7(a text, b text)
+LANGUAGE SQL
+AS $$
+SELECT a = b;
+$$;
+
+CALL ptest7(least('a', 'b'), 'a');
+
+
-- various error cases
CALL version(); -- error: not a procedure
--
2.20.1
Attachments:
[text/plain] 0001-Add-collation-assignment-to-CALL-statement.patch (2.3K, ../66209b1a-db5b-d578-6674-1be789c581f5@2ndquadrant.com/2-0001-Add-collation-assignment-to-CALL-statement.patch)
download | inline diff:
From 5c68d4c057f2ed41d03faf46e20e33df24990305 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Tue, 5 Feb 2019 15:08:53 +0100
Subject: [PATCH] Add collation assignment to CALL statement
Otherwise functions that require collation information will not have
it if they are called in arguments to a CALL statement.
Reported-by: Jean-Marc Voillequin <Jean-Marc.Voillequin@moodys.com>
Discussion: https://www.postgresql.org/message-id/flat/1EC8157EB499BF459A516ADCF135ADCE39FFAC54%40LON-WGMSX712.ad.moodys.net
---
src/backend/parser/analyze.c | 2 ++
src/test/regress/expected/create_procedure.out | 7 +++++++
src/test/regress/sql/create_procedure.sql | 11 +++++++++++
3 files changed, 20 insertions(+)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 7f5773582b..e3544efb6f 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -2636,6 +2636,8 @@ transformCallStmt(ParseState *pstate, CallStmt *stmt)
true,
stmt->funccall->location);
+ assign_expr_collations(pstate, node);
+
stmt->funcexpr = castNode(FuncExpr, node);
result = makeNode(Query);
diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out
index 5b9b83839c..211a42cefa 100644
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -139,6 +139,13 @@ AS $$
SELECT NULL::int;
$$;
CALL ptest6(1, 2);
+-- collation assignment
+CREATE PROCEDURE ptest7(a text, b text)
+LANGUAGE SQL
+AS $$
+SELECT a = b;
+$$;
+CALL ptest7(least('a', 'b'), 'a');
-- various error cases
CALL version(); -- error: not a procedure
ERROR: version() is not a procedure
diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql
index b64293ed66..89b96d580f 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -101,6 +101,17 @@ CREATE PROCEDURE ptest6(a int, b anyelement)
CALL ptest6(1, 2);
+-- collation assignment
+
+CREATE PROCEDURE ptest7(a text, b text)
+LANGUAGE SQL
+AS $$
+SELECT a = b;
+$$;
+
+CALL ptest7(least('a', 'b'), 'a');
+
+
-- various error cases
CALL version(); -- error: not a procedure
--
2.20.1
view thread (5+ messages) latest in thread
Message-ID: <66209b1a-db5b-d578-6674-1be789c581f5@2ndquadrant.com>
Permalink: ../66209b1a-db5b-d578-6674-1be789c581f5@2ndquadrant.com/
Also on: postgresql.org/message-id/66209b1a-db5b-d578-6674-1be789c581f5@2ndquadrant.com
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-sql@postgresql.org
Cc: peter.eisentraut@2ndquadrant.com, tgl@sss.pgh.pa.us, Jean-Marc.Voillequin@moodys.com, pgsql-bugs@lists.postgresql.org, pgsql-sql@lists.postgresql.org
Subject: Re: Weird "could not determine which collation to use for string comparison" with LEAST/GREATEST on PG11 procedure
In-Reply-To: <66209b1a-db5b-d578-6674-1be789c581f5@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox