Re: How to assemble all fields of (any) view into a string? - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: How to assemble all fields of (any) view into a string? |
Date | |
Msg-id | d8c4e8d3-8b1b-933c-60e2-10c493c710fe@aklaver.com Whole thread Raw |
In response to | Re: How to assemble all fields of (any) view into a string? (Ken Tanzer <ken.tanzer@gmail.com>) |
Responses |
Re: How to assemble all fields of (any) view into a string?
(Ken Tanzer <ken.tanzer@gmail.com>)
|
List | pgsql-general |
On 09/07/2016 01:36 PM, Ken Tanzer wrote: > > > On Wed, Sep 7, 2016 at 1:22 PM, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 09/07/2016 01:15 PM, Ken Tanzer wrote: > > Hi. Using version 9.2. I'm trying to create a function that > will take > a record from any view and assemble it into a string, for export to > another system. For example, this view: > > \d ebh_gain > > View "public.ebh_gain" > Column | Type | Modifiers > -------------------+---------------+----------- > reporting_unit_id | character(3) | > case_id | character(10) | > event_date | character(8) | > ids_score | character(1) | > eds_score | character(1) | > sds_score | character(1) | > kc_auth_number | integer | > king_county_id | integer | > > would get converted into a string with all the fields concatenated > together, and space-padded to their full lengths. > > > I think an example is needed. I was thinking you wanted the field > values transformed, but the below seems to indicate something different. > > No transformation is needed, except for padding the fields out to their > maximum lengths. So for example with these values > > ('AA','1243','20160801','2','1','1',37,24) > > I need a string created that looks like this: > > 'AA 1243 201608012113724' > > I have a whole bunch of views that I need to do this for, and am hoping > to avoid coding something specific for each one. I can do it relatively easy in plpythonu: production=# \d str_test Table "history.str_test" Column | Type | Modifiers -------------------+---------------+----------- reporting_unit_id | character(3) | case_id | character(10) | event_date | character(8) | production=# insert into str_test values ('1', '1234', '09/07/16'); INSERT 0 1 DO $$ rs = plpy.execute("SELECT * FROM str_test", 1) cols = rs.colnames() plpy.notice(rs.colnames()) str_out = "" for col in cols: str_out += str(rs[0][col]) plpy.notice(str_out) $$ LANGUAGE plpythonu; NOTICE: ['reporting_unit_id', 'case_id', 'event_date'] CONTEXT: PL/Python anonymous code block NOTICE: 1 1234 09/07/16 CONTEXT: PL/Python anonymous code block DO > > > > > > My original idea was to do this in TCL by passing a record and a > view > name. The function would then look up the columns in the > information_schema, and use that info to assemble and return the > string. But it looks like TCL functions won't accept a record as an > argument. > > Any suggestions or advice most welcome. Thanks! > > Ken > -- > AGENCY Software > A Free Software data system > By and for non-profits > /http://agency-software.org// > /https://agency-software.org/demo/client/ > <https://agency-software.org/demo/client/> > ken.tanzer@agency-software.org > <mailto:ken.tanzer@agency-software.org> > <mailto:ken.tanzer@agency-software.org > <mailto:ken.tanzer@agency-software.org>> > (253) 245-3801 <tel:%28253%29%20245-3801> > > Subscribe to the mailing list > <mailto:agency-general-request@lists.sourceforge.net > <mailto:agency-general-request@lists.sourceforge.net>?body=subscribe> > to > learn more about AGENCY or > follow the discussion. > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > > > -- > AGENCY Software > A Free Software data system > By and for non-profits > /http://agency-software.org// > /https://agency-software.org/demo/client/ > ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org> > (253) 245-3801 > > Subscribe to the mailing list > <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to > learn more about AGENCY or > follow the discussion. -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: