On Wed, Sep 7, 2016 at 3:18 PM, Adrian Klaver <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>> 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 without having the function select the individual record. Ideally: SELECT my_cat(ebh_gain) FROM ebh_gain; 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. > >
