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

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

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/&gt;
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:

On 9/7/16 5:32 PM, Ken Tanzer wrote:

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.

I think pltcl is actually the only one of our standard PLs that can't take
"record". There's no intrinsic reason for that, it just hasn't gotten the
love the other PLs have.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
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
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

On 09/07/2016 03:38 PM, Jim Nasby 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.

But can they be anonymous types?

Ken wants this to be generic so any tables record can be supplied as an
argument. In plpythonu it seems you need to declare the table type when
supplying the record.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

and how would the calling program even know what the fields are if its
not aware of the field lengths?

what if a field is type 'text' ? or an array type ?

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

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

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
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
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.