From bfaf3a15a4880fcea6a2d669dbc74a71299e346f Mon Sep 17 00:00:00 2001 From: Marko Kreen Date: Thu, 26 Jun 2008 08:57:45 +0000 Subject: [PATCH] Support dynamic record as return type. Now PL/Proxy allows function defined as RETURNS RECORD without OUT parameters. Such functions need actual result column types specified with AS clause: SELECT * FROM func() AS (col1 type1, col2 type2); PL/Proxy needs to detect the actual type on each call and pass it to remote database. Current implementation caches last record type, drops it if type changes. Original patch by Lei Yonghua, applied with some modifications. --- Makefile | 2 +- expected/plproxy_dynamic_record.out | 51 +++++++++++++++++++++ sql/plproxy_dynamic_record.sql | 43 +++++++++++++++++ src/function.c | 71 +++++++++++++++++++++++++++++ src/plproxy.h | 5 ++ src/query.c | 17 +++++++ src/type.c | 30 +++++++++++- 7 files changed, 217 insertions(+), 2 deletions(-) create mode 100644 expected/plproxy_dynamic_record.out create mode 100644 sql/plproxy_dynamic_record.sql diff --git a/Makefile b/Makefile index 66d6be6..0b51f4d 100644 --- a/Makefile +++ b/Makefile @@ -31,7 +31,7 @@ DIST_FILES = Makefile src/plproxy.h src/rowstamp.h src/scanner.l src/parser.y \ # regression testing setup REGRESS = plproxy_init plproxy_test plproxy_select plproxy_many \ - plproxy_errors plproxy_clustermap + plproxy_errors plproxy_clustermap plproxy_dynamic_record REGRESS_OPTS = --load-language=plpgsql # load PGXS makefile diff --git a/expected/plproxy_dynamic_record.out b/expected/plproxy_dynamic_record.out new file mode 100644 index 0000000..d7ef1af --- /dev/null +++ b/expected/plproxy_dynamic_record.out @@ -0,0 +1,51 @@ +-- dynamic query support testing +create or replace function dynamic_query(q text) +returns setof record as $x$ + cluster 'map0'; + run on all; +$x$ language plproxy; +\c test_part0 +create or replace function dynamic_query(q text) +returns setof record as $x$ +declare + ret record; +begin + for ret in execute q loop + return next ret; + end loop; + return; +end; +$x$ language plpgsql; +create table dynamic_query_test ( + id integer, + username text, + other text +); +insert into dynamic_query_test values ( 1, 'user1', 'blah'); +insert into dynamic_query_test values ( 2, 'user2', 'foo'); +\c regression +select * from dynamic_query('select * from dynamic_query_test') as (id integer, username text, other text); + id | username | other +----+----------+------- + 1 | user1 | blah + 2 | user2 | foo +(2 rows) + +select * from dynamic_query('select id, username from dynamic_query_test') as foo(id integer, username text); + id | username +----+---------- + 1 | user1 + 2 | user2 +(2 rows) + +-- test errors +select * from dynamic_query('select * from dynamic_query_test'); +ERROR: a column definition list is required for functions returning "record" +create or replace function dynamic_query_select() +returns setof record as $x$ + cluster 'map0'; + run on all; + select id, username from dynamic_query_test; +$x$ language plproxy; +select * from dynamic_query_select() as (id integer, username text); +ERROR: PL/Proxy function public.dynamic_query_select(0): SELECT statement not allowed for dynamic RECORD functions diff --git a/sql/plproxy_dynamic_record.sql b/sql/plproxy_dynamic_record.sql new file mode 100644 index 0000000..23a6448 --- /dev/null +++ b/sql/plproxy_dynamic_record.sql @@ -0,0 +1,43 @@ +-- dynamic query support testing +create or replace function dynamic_query(q text) +returns setof record as $x$ + cluster 'map0'; + run on all; +$x$ language plproxy; + +\c test_part0 +create or replace function dynamic_query(q text) +returns setof record as $x$ +declare + ret record; +begin + for ret in execute q loop + return next ret; + end loop; + return; +end; +$x$ language plpgsql; +create table dynamic_query_test ( + id integer, + username text, + other text +); +insert into dynamic_query_test values ( 1, 'user1', 'blah'); +insert into dynamic_query_test values ( 2, 'user2', 'foo'); + +\c regression +select * from dynamic_query('select * from dynamic_query_test') as (id integer, username text, other text); +select * from dynamic_query('select id, username from dynamic_query_test') as foo(id integer, username text); + + +-- test errors +select * from dynamic_query('select * from dynamic_query_test'); + +create or replace function dynamic_query_select() +returns setof record as $x$ + cluster 'map0'; + run on all; + select id, username from dynamic_query_test; +$x$ language plproxy; +select * from dynamic_query_select() as (id integer, username text); + diff --git a/src/function.c b/src/function.c index 8a4ea3c..6371b10 100644 --- a/src/function.c +++ b/src/function.c @@ -131,6 +131,19 @@ fn_cache_delete(ProxyFunction *func) Assert(hentry != NULL); } +/* check if function returns untyped RECORD which needs the AS clause */ +static bool +fn_returns_dynamic_record(HeapTuple proc_tuple) +{ + Form_pg_proc proc_struct; + proc_struct = (Form_pg_proc) GETSTRUCT(proc_tuple); + if (proc_struct->prorettype == RECORDOID + && (heap_attisnull(proc_tuple, Anum_pg_proc_proargmodes) + || heap_attisnull(proc_tuple, Anum_pg_proc_proargnames))) + return true; + return false; +} + /* * Allocate storage for function. * @@ -157,6 +170,9 @@ fn_new(FunctionCallInfo fcinfo, HeapTuple proc_tuple) f->oid = fcinfo->flinfo->fn_oid; plproxy_set_stamp(&f->stamp, proc_tuple); + if (fn_returns_dynamic_record(proc_tuple)) + f->dynamic_record = 1; + MemoryContextSwitchTo(old_ctx); return f; @@ -291,8 +307,15 @@ fn_get_return_type(ProxyFunction *func, MemoryContext old_ctx; int natts; + + /* + * get_call_result_type() will return newly allocated tuple, + * except in case of untyped RECORD functions. + */ old_ctx = MemoryContextSwitchTo(func->ctx); rtc = get_call_result_type(fcinfo, &ret_oid, &ret_tup); + if (func->dynamic_record && ret_tup) + ret_tup = CreateTupleDescCopy(ret_tup); MemoryContextSwitchTo(old_ctx); switch (rtc) @@ -314,6 +337,46 @@ fn_get_return_type(ProxyFunction *func, } } +/* + * Check if cached ->ret_composite is valid, refresh if needed. + */ +static void +fn_refresh_record(FunctionCallInfo fcinfo, + ProxyFunction *func, + HeapTuple proc_tuple) +{ + + TypeFuncClass rtc; + TupleDesc tuple_current, tuple_cached; + MemoryContext old_ctx; + int natts; + + /* + * Compare cached tuple to current one. + */ + tuple_cached = func->ret_composite->tupdesc; + rtc = get_call_result_type(fcinfo, NULL, &tuple_current); + Assert(rtc == TYPEFUNC_COMPOSITE); + if (equalTupleDescs(tuple_current, tuple_cached)) + return; + + /* move to function context */ + old_ctx = MemoryContextSwitchTo(func->ctx); + tuple_current = CreateTupleDescCopy(tuple_current); + MemoryContextSwitchTo(old_ctx); + + /* release old data */ + plproxy_free_composite(func->ret_composite); + pfree(func->result_map); + pfree(func->remote_sql); + + /* construct new data */ + func->ret_composite = plproxy_composite_info(func, tuple_current); + natts = func->ret_composite->tupdesc->natts; + func->result_map = plproxy_func_alloc(func, natts * sizeof(int)); + func->remote_sql = plproxy_standard_query(func, true); +} + /* Show part of compilation -- get source and parse */ static ProxyFunction * fn_compile(FunctionCallInfo fcinfo, @@ -340,6 +403,9 @@ fn_compile(FunctionCallInfo fcinfo, /* parse body */ fn_parse(f, proc_tuple); + if (f->dynamic_record && f->remote_sql) + plproxy_error(f, "SELECT statement not allowed for dynamic RECORD functions"); + /* create SELECT stmt if not specified */ if (f->remote_sql == NULL) f->remote_sql = plproxy_standard_query(f, true); @@ -401,6 +467,11 @@ plproxy_compile(FunctionCallInfo fcinfo, bool validate) /* now its safe to drop reference */ partial_func = NULL; } + else if (f->dynamic_record) + { + /* in case of untyped RECORD, check if cached type is valid */ + fn_refresh_record(fcinfo, f, proc_tuple); + } ReleaseSysCache(proc_tuple); diff --git a/src/plproxy.h b/src/plproxy.h index b720909..6ee1e01 100644 --- a/src/plproxy.h +++ b/src/plproxy.h @@ -226,6 +226,9 @@ typedef struct ProxyFunction ProxyType **arg_types; /* Info about arguments */ char **arg_names; /* Argument names, may contain NULLs */ + /* if the function returns untyped RECORD that needs AS clause */ + bool dynamic_record; + /* One of them is defined, other NULL */ ProxyType *ret_scalar; /* Type info for scalar return val */ ProxyComposite *ret_composite; /* Type info for composite return val */ @@ -292,6 +295,8 @@ ProxyType *plproxy_find_type_info(ProxyFunction *func, Oid oid, bool for_send); char *plproxy_send_type(ProxyType *type, Datum val, bool allow_bin, int *len, int *fmt); Datum plproxy_recv_type(ProxyType *type, char *str, int len, bool bin); HeapTuple plproxy_recv_composite(ProxyComposite *meta, char **values, int *lengths, int *fmts); +void plproxy_free_type(ProxyType *type); +void plproxy_free_composite(ProxyComposite *meta); /* cluster.c */ void plproxy_cluster_cache_init(void); diff --git a/src/query.c b/src/query.c index 142494f..ccd29cf 100644 --- a/src/query.c +++ b/src/query.c @@ -215,6 +215,23 @@ plproxy_standard_query(ProxyFunction *func, bool add_types) } appendStringInfoChar(&sql, ')'); + /* + * Untyped RECORD needs types specified in AS (..) clause. + */ + if (func->dynamic_record) + { + ProxyComposite *t = func->ret_composite; + appendStringInfo(&sql, " as ("); + for (i = 0; i < t->tupdesc->natts; i++) + { + appendStringInfo(&sql, "%s%s %s", + ((i > 0) ? ", " : ""), + t->name_list[i], + t->type_list[i]->name); + } + appendStringInfoChar(&sql, ')'); + } + if (func->ret_scalar) appendStringInfo(&sql, " r"); diff --git a/src/type.c b/src/type.c index 1dab85b..858068c 100644 --- a/src/type.c +++ b/src/type.c @@ -101,8 +101,36 @@ plproxy_composite_info(ProxyFunction *func, TupleDesc tupdesc) return ret; } +void +plproxy_free_composite(ProxyComposite *rec) +{ + int i; + int natts = rec->tupdesc->natts; + + for (i = 0; i < natts; i++) + { + plproxy_free_type(rec->type_list[i]); + pfree(rec->name_list[i]); + } + pfree(rec->type_list); + pfree(rec->name_list); + FreeTupleDesc(rec->tupdesc); + pfree(rec); +} + +void +plproxy_free_type(ProxyType *type) +{ + if (type->name) + pfree(type->name); + + /* hopefully I/O functions do not use ->fn_extra */ + + pfree(type); +} + /* - * Build result tuplw from binary or CString values. + * Build result tuple from binary or CString values. * * Based on BuildTupleFromCStrings. */ -- 2.39.5