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

On 09/07/2016 01:36 PM, Ken Tanzer wrote:
>
>
> On Wed, Sep 7, 2016 at 1:22 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)tanzer(at)agency-software(dot)org
> <mailto:ken(dot)tanzer(at)agency-software(dot)org>
> <mailto:ken(dot)tanzer(at)agency-software(dot)org
> <mailto:ken(dot)tanzer(at)agency-software(dot)org>>
> (253) 245-3801 <tel:%28253%29%20245-3801>
>
> Subscribe to the mailing list
> <mailto:agency-general-request(at)lists(dot)sourceforge(dot)net
> <mailto:agency-general-request(at)lists(dot)sourceforge(dot)net>?body=subscribe>
> to
> learn more about AGENCY or
> follow the discussion.
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://agency-software.org/demo/client/
> ken(dot)tanzer(at)agency-software(dot)org <mailto:ken(dot)tanzer(at)agency-software(dot)org>
> (253) 245-3801
>
> Subscribe to the mailing list
> <mailto:agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com