Re: How to assemble all fields of (any) view into a string? - Mailing list pgsql-general

On 09/07/2016 03:32 PM, Ken Tanzer wrote:
>
>
> On Wed, Sep 7, 2016 at 3:18 PM, Adrian Klaver <adrian.klaver@aklaver.com
> <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@aklaver.com <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.

> 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?

>
> 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@aklaver.com