Re: How to assemble all fields of (any) view into a string?

On 09/07/2016 04:25 PM, Jim Nasby wrote:
> On 9/7/16 6:07 PM, Ken Tanzer wrote:
>> ERROR:  PL/Python functions cannot accept type record
>
> Ugh, yeah... that won't work. plperl might be able to do it, but I
> suspect you're going to be stuck pulling the size info out of
> info_schema or the catalog.
>
> Actually, there is a way you could hack this via plpython; pass the row
> in as text as well as the relation (regclass is good for that). You
> could then do plpy.execute('SELECT (%::%).*'.format(row_text,
> relation)); that should give you a dict just like Adrian's example did.
>
> It would be nice if there was a function that accepted something with a
> row descriptor and spit out the details of the descriptor.
> http://pgxn.org/dist/colnames/doc/colnames.html comes close; if you know
> much about C at all it shouldn't be hard to add a function to that
> extension that returned the full details of the row. That and converting
> the row to JSON would make it relatively easy to accomplish what you
> want in a plpgsql (or maybe even plsql) function.

Getting closer:

CREATE OR REPLACE FUNCTION public.str_concat(r json)
  RETURNS text
  LANGUAGE plpythonu
AS $function$
import json
j = json.loads(r)
str_out = ""
plpy.notice(type(j))
for col in j:
     str_out += j[col]
return str_out
$function$

production=# select str_concat(row_to_json(t)) from str_test as t;
NOTICE:  <type 'dict'>
CONTEXT:  PL/Python function "str_concat"
       str_concat
-----------------------
  09/07/161234      1



--
Adrian Klaver
adrian.klaver@aklaver.com