Thread: How to assemble all fields of (any) view into a string?
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.
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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
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_gainView "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
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Wed, Sep 7, 2016 at 3:46 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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.
Oh, I didn't see how it would pick up the padding, but great!
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?
I would like one record converted per function call. But of course to be able to generate multiple ones in a select:
INSERT INTO holding_table my_cat(ebh_gain,'ebh_gain') FROM ebh_gain WHERE needs_to_be_exported...
Thanks,
Ken
--
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
AGENCY Software
A Free Software data system
By and for non-profits
learn more about AGENCY or
follow the discussion.
On Wed, Sep 7, 2016 at 3:38 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 9/7/16 5:32 PM, Ken Tanzer wrote: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.
A PL that can accept composite types (such as plpythonu) should be able to do this.
OK, python is new to me, so I'm trying to dig into it. I installed it, and tried declaring a function:
CREATE OR REPLACE FUNCTION ebh_transaction_assemble( varchar, record ) RETURNS varchar AS $$...
but got the same error:
ERROR: PL/Python functions cannot accept type record
Is there some other way I should be specifying this? Or do I need a more recent version of Postgres (I'm on 9.2) to do this? Thanks.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Wed, Sep 7, 2016 at 4:39 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 09/07/2016 04:25 PM, Jim Nasby wrote:On 9/7/16 6:07 PM, Ken Tanzer wrote:ERROR: PL/Python functions cannot accept type record
Ugh, yeah... that won't work. plperl might be able to do it, but I
suspect you're going to be stuck pulling the size info out of
info_schema or the catalog.
Actually, there is a way you could hack this via plpython; pass the row
in as text as well as the relation (regclass is good for that). You
could then do plpy.execute('SELECT (%::%).*'.format(row_text,
relation)); that should give you a dict just like Adrian's example did.
It would be nice if there was a function that accepted something with a
row descriptor and spit out the details of the descriptor.
http://pgxn.org/dist/colnames/doc/colnames.html comes close; if you know
much about C at all it shouldn't be hard to add a function to that
extension that returned the full details of the row. That and converting
the row to JSON would make it relatively easy to accomplish what you
want in a plpgsql (or maybe even plsql) function.
Getting closer:
CREATE OR REPLACE FUNCTION public.str_concat(r json)
RETURNS text
LANGUAGE plpythonu
AS $function$
import json
j = json.loads(r)
str_out = ""
plpy.notice(type(j))
for col in j:
str_out += j[col]
return str_out
$function$
production=# select str_concat(row_to_json(t)) from str_test as t;
NOTICE: <type 'dict'>
CONTEXT: PL/Python function "str_concat"
str_concat
-----------------------
09/07/161234 1
That's great, and was more than enough to get me started. This is what I ended up with, which I think does the trick. Thanks so much!
CREATE OR REPLACE FUNCTION ebh_transaction_assemble( view_name varchar, j_rec json ) RETURNS varchar LANGUAGE plpythonu AS $$
import json
j = json.loads(j_rec)
str_out = ""
query="SELECT column_name,character_maximum_length,ordinal_position,data_type FROM information_schema.columns WHERE table_name ='" + view_name + "' ORDER BY ordinal_position"
cols = plpy.execute(query)
for col in cols:
dtype = col["data_type"]
dlength = col["character_maximum_length"]
dname = col["column_name"]
dvalue = str(j[dname])
if ( dvalue == "None" ) : dvalue = ""
if ( dtype == "character" ) : dvalue = dvalue.ljust(dlength)
str_out += dvalue
return str_out
$$
;
--
Adrian Klaver
adrian.klaver@aklaver.com
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Wed, Sep 7, 2016 at 4:31 PM, John R Pierce <pierce@hogranch.com> wrote:
On 9/7/2016 1:36 PM, Ken Tanzer wrote:No transformation is needed, except for padding the fields out to their maximum lengths.
without accessing metadata, how would you know what those maximum lengths are??
I agree you'd need metadata. My OP mentioned looking this up in the information schema
and how would the calling program even know what the fields are if its not aware of the field lengths?
Do you mean how would the function know? I didn't see a better way than by explicitly passing the view name.
what if a field is type 'text' ? or an array type ?
They won't be. This has to work for a very specific set of views I've created, not for any view in the universe!
Cheers,
Ken
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.