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

On Wed, Sep 7, 2016 at 3:46 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 09/07/2016 03:32 PM, Ken Tanzer wrote:
>
>>
>>
>> On Wed, Sep 7, 2016 at 3:18 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> 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>
>> <mailto: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
>>
>> Yeah, that and a trip to the information schema to pad out the fields
>> would get me the string I need. But I was hoping to be able to do this
>>
>
> Well the above has the padding already there, though that assumes char(x)
> fields.

Oh, I didn't see how it would pick up the padding, but great!

>
>
> without having the function select the individual record. Ideally:
>>
>> SELECT my_cat(ebh_gain) FROM ebh_gain;
>>
>
> So do you want one record to be converted at a time or many?
>
>
I would like one record converted per function call. But of course to be
able to generate multiple ones in a select:

INSERT INTO holding_table my_cat(ebh_gain,'ebh_gain') FROM ebh_gain WHERE
needs_to_be_exported...

Thanks,
Ken

>
>> or, at least somewhat more realistically:
>>
>> SELECT my_cat(ebh_gain,'ebh_gain') FROM ebh_gain;
>>
>> I know TCL and probably Python and others can work with a record as a
>> trigger function. But TCL doesn't seem to accept a record as an
>> argument. Can any of the other languages that could also accomplish
>> this function? Or some other way? Thanks.
>>
>>
>>
>>
>
> --
> 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.