[PostgreSQL8.4] Crosstab... Help me please. [long]

Peca's

unread,

Oct 10, 2009, 6:12:27 AM10/10/09

to

Hello everyone,

I'm breaking my head to write a function that returns a table
PG8.4 well-formatted to avoid using PHP code.
We illustrate the structure of the tables (I have reported only the
important fields):

Tables:
table_id serial PK
table_name varchar(50) unique

Fields:
field_id serial PK
field_name varchar(50) unique

Table_fields:
table_field_id serial PK
table_id FK(tables.table_id)
field_id FK(field.field_id)

Unique(table_id,field_id)

Codes:
code_id serial PK
code_name varchar(50) unique

Table_values:
table_value_id serial PK
code_id FK(codes.code_id)
table_fields_id FK(table_fields.table_field_id)
value varchar(50)

Now I want to create a function where I can pass the "table_id" and it
create a structure with a variable number of columns resulting from the
table "Table_fields.

Es:
id serial PK
code_name varchar
table_name vachar
field1
field2
field3
...
...
...
fieldN

The number of fields change from table to table for an even number of
reports from the past with the table fields. I need the crosstab
because I do not want all the fields as a record but the last example
will be the column names and the value should be reported as a record.

For example "f_crosstab_value(table_id)":

The values are an example.
Is without saying that every "table_name" will have different product
names and properties (fields) with different specifications. For those
codes that have no value, since the structure of table will equal for
all records, I will leave the field blank or null. The structure also
allows you to add new properties without affecting the physical table.

Someone can help me to write this function?

Thanks!

Thomas Kellerer

unread,

Oct 10, 2009, 6:27:52 AM10/10/09

to

Peca's wrote on 10.10.2009 12:12:
> Now I want to create a function where I can pass the "table_id" and it
> create a structure with a variable number of columns resulting from the
> table "Table_fields.
>

Check out the the tablefunc contrib module, it contains a function that might do what you want (crosstab)

http://www.postgresql.org/docs/current/static/tablefunc.html

Thomas

Peca's

unread,

Oct 10, 2009, 10:34:31 AM10/10/09

to

> Check out the the tablefunc contrib module, it contains a function that
> might do what you want (crosstab)
>
> http://www.postgresql.org/docs/current/static/tablefunc.html

Yes I know the crosstab function for another simple case but now I don't
know the istructions for create a function for this case.

Can you help me?

Laurenz Albe

unread,

Oct 12, 2009, 11:51:29 AM10/12/09

to

Peca's wrote:

> Someone can help me to write this function?

If you want someone to write the function for you, it may be
that nobody has time and patience enough to do it.

But I guess you want to write the function yourself,
probably in PL/pgSQL.
Where do you have a problem? With writing a table function
in general? Or do you hang at a certain step?

Yours,
Laurenz Albe

Laurenz Albe

unread,

Oct 13, 2009, 4:39:41 AM10/13/09

to

I wrote:
>> Someone can help me to write this function?
>

> Where do you have a problem? With writing a table function
> in general? Or do you hang at a certain step?

I thought some more, and maybe I understood now what your
problem is or will be.

You will get difficulties with the different number and
type of the result values between different calls.

It is possible to define a function as
... RETURNS SETOF RECORD
but then you have to supply the signature when you call the function:

SELECT func(args) AS (col1 type1, col2 type2, ...)

That will probably make a generic function less useful that
you intend.

Yours,
Laurenz Albe

Peca's

unread,

Oct 13, 2009, 5:16:38 PM10/13/09

to

> It is possible to define a function as
> ... RETURNS SETOF RECORD
> but then you have to supply the signature when you call the function:
>
> SELECT func(args) AS (col1 type1, col2 type2, ...)
>
> That will probably make a generic function less useful that
> you intend.

Tomorrow I try your solution and I tell you the result

Tx