On Wed, Sep 7, 2016 at 3:46 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 09/07/2016 03:32 PM, Ken Tanzer wrote: > >> >> >> On Wed, Sep 7, 2016 at 3:18 PM, Adrian Klaver <adrian.kla...@aklaver.com >> <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> <mailto:adrian.kla...@aklaver.com >> >> <mailto:adrian.kla...@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.kla...@aklaver.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.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
