Re: How to assemble all fields of (any) view into a string? - Mailing list pgsql-general

From Ken Tanzer
Subject Re: How to assemble all fields of (any) view into a string?
Date
Msg-id CAD3a31U5HRwob=1wOq=CJ0O=1UZZZZeHD7APmkqkuLONq8Q8ng@mail.gmail.com
Whole thread Raw
In response to Re: How to assemble all fields of (any) view into a string?  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
On Wed, Sep 7, 2016 at 4:39 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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

That's great, and was more than enough to get me started.  This is what I ended up with, which I think does the trick.  Thanks so much!

CREATE OR REPLACE FUNCTION ebh_transaction_assemble( view_name varchar, j_rec json ) RETURNS varchar LANGUAGE plpythonu AS $$
import json
j = json.loads(j_rec)
str_out = ""
query="SELECT column_name,character_maximum_length,ordinal_position,data_type FROM information_schema.columns WHERE table_name ='" + view_name + "' ORDER BY ordinal_position"
cols =  plpy.execute(query)
for col in cols:
  dtype = col["data_type"]
  dlength = col["character_maximum_length"]
  dname = col["column_name"]
  dvalue = str(j[dname])
  if ( dvalue == "None" ) : dvalue = ""
  if ( dtype == "character" ) : dvalue = dvalue.ljust(dlength)
  str_out += dvalue
return str_out
$$
;

 
--
Adrian Klaver
adrian.klaver@aklaver.com



--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

pgsql-general by date:

Previous
From: "PICCI Guillermo SNOOP"
Date:
Subject: qustion about pgaudit
Next
From: Ken Tanzer
Date:
Subject: Re: How to assemble all fields of (any) view into a string?