Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Wed, Sep 7, 2016 at 3:18 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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>> 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.
 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux