Home > mailing lists

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

From	Ken Tanzer
Subject	Re: How to assemble all fields of (any) view into a string?
Date	September 7, 2016 23:37:49
Msg-id	CAD3a31UhbOJeSoLYokx99k4Td9FFOUr=T-Gm-6n5trDPyi1B8Q@mail.gmail.com Whole thread Raw
In response to	Re: How to assemble all fields of (any) view into a string? (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses	Re: How to assemble all fields of (any) view into a string? Re: How to assemble all fields of (any) view into a string?
List	pgsql-general

Tree view

On Wed, Sep 7, 2016 at 1:22 PM, Adrian Klaver <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.

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