From: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to assemble all fields of (any) view into a string? |
Date: | 2016-09-08 17:13:32 |
Message-ID: | CAD3a31U5HRwob=1wOq=CJ0O=1UZZZZeHD7APmkqkuLONq8Q8ng@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Sep 7, 2016 at 4:39 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
From | Date | Subject | |
---|---|---|---|
Next Message | Ashish Chauhan | 2016-09-08 17:14:47 | Re: Setup pgpool-II with streaming replication |
Previous Message | PICCI Guillermo SNOOP | 2016-09-08 15:19:59 | qustion about pgaudit |