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

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



>
>
>
>
>
>         My original idea was to do this in TCL by passing a record and a
>         view
>         name.  The function would then look up the columns in the
>         information_schema, and use that info to assemble and return the
>         string.  But it looks like TCL functions won't accept a record as an
>         argument.
>
>         Any suggestions or advice most welcome.  Thanks!
>
>         Ken
>         --
>         AGENCY Software
>         A Free Software data system
>         By and for non-profits
>         /http://agency-software.org//
>         /https://agency-software.org/demo/client/
>         <https://agency-software.org/demo/client/>
>         ken.tanzer@agency-software.org
>         <mailto:ken.tanzer@agency-software.org>
>         <mailto:ken.tanzer@agency-software.org
>         <mailto:ken.tanzer@agency-software.org>>
>         (253) 245-3801 <tel:%28253%29%20245-3801>
>
>         Subscribe to the mailing list
>         <mailto:agency-general-request@lists.sourceforge.net
>         <mailto:agency-general-request@lists.sourceforge.net>?body=subscribe>
>         to
>         learn more about AGENCY or
>         follow the discussion.
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://agency-software.org/demo/client/
> ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
>
> Subscribe to the mailing list
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


--
Adrian Klaver
adrian.klaver@aklaver.com