On Wed, Sep 7, 2016 at 3:46 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 09/07/2016 03:32 PM, Ken Tanzer wrote:
On Wed, Sep 7, 2016 at 3:18 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@aklaver.com >> wrote:
On 09/07/2016 01:36 PM, Ken Tanzer wrote:
On Wed, Sep 7, 2016 at 1:22 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.com >
<mailto: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
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.klaver@xxxxxxxxxxx
AGENCY Software
A Free Software data system
By and for non-profits
learn more about AGENCY or
follow the discussion.
