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)":

table_value_id| code_name | table_name | colour | heavy | shape | ...
1 | potatoes | vegetables| 5 | 100 | sphere | ...
2 | insalata | vegetables| | 50 | various | ...

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!

Peca's wrote on 10.10.2009 12:12:
Check out the the tablefunc contrib module, it contains a function that might do what you want (crosstab)

http://www.postgresql.org/docs/curre...tablefunc.html

Thomas

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?

Peca's wrote:

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

I wrote:
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

Tomorrow I try your solution and I tell you the result

Tx