On 4/25/15 4:50 PM, Tom Lane wrote:
Well, we already support local variables of type RECORD in plpgsql, so
it's not immediately clear to me that function arguments would be much
worse. There are a lot of deficiencies with the RECORD-local-variable
implementation: if you try to change the actual RECORD type from one call
to the next you'll probably have problems. But it seems like we could
avoid that for function arguments by treating RECORD as a polymorphic
argument type, and thereby generating a separate set of plan trees for
each actual record type passed to the function within a given session.
So in principle it ought to work better than the local-variable case does
today.
In short I suspect that Jim is right and this has more to do with a
shortage of round tuits than any fundamental problem.
I took a stab at plpgsql and it seems to work ok... but I'm not sure
it's terribly valuable because you end up with an anonymous record
instead of something that points back to what you handed it. The 'good'
news is it doesn't seem to blow up on successive calls with different
arguments...
Not sure about the SQL-function case. That might be even easier because
functions.c doesn't try to cache plans across queries; or maybe not.
This on the other hand was rather easy. It's not horribly useful due to
built-in restrictions on dealing with record, but that's certainly not
plsql's fault, and this satisfies my initial use case of
create function cn(record) returns bigint language sql as $$
SELECT count(*)
FROM json_each_text( row_to_json($1) ) a
WHERE value IS NULL $$;
Attached patches both pass make check. The plpgsql is WIP, but I think
the SQL one is OK.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
>From cc1776d4963b0ae837f61320ba36a6ff3ad7a7cb Mon Sep 17 00:00:00 2001
From: Jim Nasby <jim.na...@bluetreble.com>
Date: Mon, 27 Apr 2015 18:54:51 -0500
Subject: [PATCH] Allow SQL functions to accept a record
---
src/backend/catalog/pg_proc.c | 10 +++++++---
src/test/regress/expected/create_function_3.out | 17 ++++++++++++++---
src/test/regress/sql/create_function_3.sql | 6 +++++-
3 files changed, 26 insertions(+), 7 deletions(-)
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index 1229829..daf3297 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -870,14 +870,18 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
errmsg("SQL functions cannot return type %s",
format_type_be(proc->prorettype))));
- /* Disallow pseudotypes in arguments */
- /* except for polymorphic */
+ /*
+ * Disallow pseudotypes in arguments except for polymorphic and record.
In
+ * the context of validating a function, record may as well be
polymorphic,
+ * so treat it as such.
+ */
haspolyarg = false;
for (i = 0; i < proc->pronargs; i++)
{
if (get_typtype(proc->proargtypes.values[i]) == TYPTYPE_PSEUDO)
{
- if (IsPolymorphicType(proc->proargtypes.values[i]))
+ if (IsPolymorphicType(proc->proargtypes.values[i]) ||
+ proc->proargtypes.values[i] == RECORDOID)
haspolyarg = true;
else
ereport(ERROR,
diff --git a/src/test/regress/expected/create_function_3.out
b/src/test/regress/expected/create_function_3.out
index 6a4352c..cd370e3 100644
--- a/src/test/regress/expected/create_function_3.out
+++ b/src/test/regress/expected/create_function_3.out
@@ -16,16 +16,26 @@ CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE
'sql'
AS 'SELECT $1[0]::int';
CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql'
AS 'SELECT false';
+CREATE FUNCTION functest_A_4(record) RETURNS regtype LANGUAGE 'sql'
+ AS 'SELECT pg_catalog.pg_typeof($1)';
+SELECT functest_A_4(NULL::pg_catalog.pg_class);
+ functest_a_4
+--------------
+ pg_class
+(1 row)
+
SELECT proname, prorettype::regtype, proargtypes::regtype[] FROM pg_proc
WHERE oid in ('functest_A_1'::regproc,
'functest_A_2'::regproc,
- 'functest_A_3'::regproc) ORDER BY proname;
+ 'functest_A_3'::regproc,
+ 'functest_A_4'::regproc) ORDER BY proname;
proname | prorettype | proargtypes
--------------+------------+-------------------
functest_a_1 | boolean | [0:1]={text,date}
functest_a_2 | integer | [0:0]={text[]}
functest_a_3 | boolean | {}
-(3 rows)
+ functest_a_4 | regtype | [0:0]={record}
+(4 rows)
--
-- IMMUTABLE | STABLE | VOLATILE
@@ -219,10 +229,11 @@ SELECT routine_name, ordinal_position, parameter_name,
parameter_default
-- Cleanups
DROP SCHEMA temp_func_test CASCADE;
-NOTICE: drop cascades to 19 other objects
+NOTICE: drop cascades to 20 other objects
DETAIL: drop cascades to function functest_a_1(text,date)
drop cascades to function functest_a_2(text[])
drop cascades to function functest_a_3()
+drop cascades to function functest_a_4(record)
drop cascades to function functest_b_1(integer)
drop cascades to function functest_b_2(integer)
drop cascades to function functest_b_3(integer)
diff --git a/src/test/regress/sql/create_function_3.sql
b/src/test/regress/sql/create_function_3.sql
index 86d69ba..1af911a 100644
--- a/src/test/regress/sql/create_function_3.sql
+++ b/src/test/regress/sql/create_function_3.sql
@@ -19,10 +19,14 @@ CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE
'sql'
AS 'SELECT $1[0]::int';
CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql'
AS 'SELECT false';
+CREATE FUNCTION functest_A_4(record) RETURNS regtype LANGUAGE 'sql'
+ AS 'SELECT pg_catalog.pg_typeof($1)';
+SELECT functest_A_4(NULL::pg_catalog.pg_class);
SELECT proname, prorettype::regtype, proargtypes::regtype[] FROM pg_proc
WHERE oid in ('functest_A_1'::regproc,
'functest_A_2'::regproc,
- 'functest_A_3'::regproc) ORDER BY proname;
+ 'functest_A_3'::regproc,
+ 'functest_A_4'::regproc) ORDER BY proname;
--
-- IMMUTABLE | STABLE | VOLATILE
--
2.1.2
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index 650cc48..de5fa54 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -430,7 +430,8 @@ do_compile(FunctionCallInfo fcinfo,
/* (note we already replaced polymorphic types)
*/
/* (build_variable would do this, but wrong
message) */
if (argdtype->ttype != PLPGSQL_TTYPE_SCALAR &&
- argdtype->ttype != PLPGSQL_TTYPE_ROW)
+ argdtype->ttype != PLPGSQL_TTYPE_ROW &&
+ argdtype->ttype != PLPGSQL_TTYPE_REC)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("PL/pgSQL functions
cannot accept type %s",
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index deefb1f..4c35f44 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -335,6 +335,28 @@ plpgsql_exec_function(PLpgSQL_function *func,
FunctionCallInfo fcinfo,
}
break;
+ case PLPGSQL_DTYPE_REC:
+ {
+ /*
+ * Target is a record variable
+ */
+ PLpgSQL_rec *rec = (PLpgSQL_rec *)
estate.datums[n];
+
+ if (!fcinfo->argnull[i])
+ {
+
exec_move_row_from_datum(&estate, rec, NULL,
+
fcinfo->arg[i]);
+ }
+ else
+ {
+ /* If source is null, just
assign nulls to the record */
+ exec_move_row(&estate, rec,
NULL, NULL, NULL);
+ }
+ /* clean up after exec_move_row() */
+ exec_eval_cleanup(&estate);
+ }
+ break;
+
default:
elog(ERROR, "unrecognized dtype: %d",
func->datums[i]->dtype);
}
diff --git a/src/pl/plpgsql/src/pl_handler.c b/src/pl/plpgsql/src/pl_handler.c
index 266c314..486d270 100644
--- a/src/pl/plpgsql/src/pl_handler.c
+++ b/src/pl/plpgsql/src/pl_handler.c
@@ -442,7 +442,8 @@ plpgsql_validator(PG_FUNCTION_ARGS)
{
if (get_typtype(argtypes[i]) == TYPTYPE_PSEUDO)
{
- if (!IsPolymorphicType(argtypes[i]))
+ if (!IsPolymorphicType(argtypes[i]) &&
+ argtypes[i] != RECORDOID)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("PL/pgSQL functions
cannot accept type %s",
diff --git a/src/test/regress/expected/plpgsql.out
b/src/test/regress/expected/plpgsql.out
index 78e5a85..28fa092 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -1828,13 +1828,28 @@ begin
k := 'foot';
return next;
end$$ language plpgsql;
-select * from f1(42);
- j | k
-----+------
- 43 | foo
- 44 | foot
+\set VERBOSITY verbose
+create function f2(r record) returns regtype as $$
+begin
+ raise notice 'r = %', r;
+ return pg_typeof(r);
+end$$ language plpgsql;
+select *, f2(row(f1.*)), f2((SELECT row(d.*) FROM pg_shdescription d LIMIT 1))
from f1(42);
+NOTICE: 00000: r = (43,foo)
+LOCATION: exec_stmt_raise, pl_exec.c:3124
+NOTICE: 00000: r = (1,1262,"default template for new databases")
+LOCATION: exec_stmt_raise, pl_exec.c:3124
+NOTICE: 00000: r = (44,foot)
+LOCATION: exec_stmt_raise, pl_exec.c:3124
+NOTICE: 00000: r = (1,1262,"default template for new databases")
+LOCATION: exec_stmt_raise, pl_exec.c:3124
+ j | k | f2 | f2
+----+------+--------+--------
+ 43 | foo | record | record
+ 44 | foot | record | record
(2 rows)
+\set VERBOSITY default
drop function f1(int);
create function duplic(in i anyelement, out j anyelement, out k anyarray) as $$
begin
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index e19e415..0f0e657 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -1623,7 +1623,15 @@ begin
return next;
end$$ language plpgsql;
-select * from f1(42);
+\set VERBOSITY verbose
+create function f2(r record) returns regtype as $$
+begin
+ raise notice 'r = %', r;
+ return pg_typeof(r);
+end$$ language plpgsql;
+
+select *, f2(row(f1.*)), f2((SELECT row(d.*) FROM pg_shdescription d LIMIT 1))
from f1(42);
+\set VERBOSITY default
drop function f1(int);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
