Hi everybody!
I'm newbie in PostgreSQL and the list, and I want to ask the following:
I have a (principal) table with ad_table_id field, and other table with primary key ad_table_id and tablename field. On the other hand, I have others tables whose primary keys are in the principal table like record_id, but it has the same numeration:
Other table 1 || Other table 2 || Principal table || Table name
_________________________________________________________________
------------------------------------------------------------------------------------------------------------------
id || id || id | record_id | ad_table_id || id | tablename
1 || 1 || 1 | 1 | 100 || 100 | c_invoice
2 || 2 || 2 | 1 | 101 || 101 | m_inout
3 || 3 || 3 | 2 | 100
|| 4 | 2 | 101
|| 5 | 3 | 100
|| 6 | 3 | 101
What I need? I need to get some fields of "other table n", and I tried with this
CREATE OR REPLACE FUNCTION schema.function() RETURNS table
(
numberDoc character varying(60),
someDate date,
neto numeric,
dateAcct date
) AS
$BODY$
DECLARE
ttablename TEXT;
sel VARCHAR (2000);
BEGIN
SET schema 'adempiere';
SELECT
t.tablename
INTO
ttablename
FROM
ad_table t JOIN
fact_acct f ON f.ad_table_id = t.ad_table_id;
sel := '
SELECT
z.documentno,
cast(min(z.dateinvoiced) AS date),
min(z.totallines),
cast(z.dateacct AS date)
FROM
fact_acct fa JOIN
|| ttablename::regclass
|| ' z ON fa.record_id = z.'
|| ttablename::regclass
|| '_id
GROUP BY
z.documentno,
z.dateacct
ORDER BY
min(z.dateinvoiced)
';
RETURN query EXECUTE sel;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
The problem is that the query is executed always with the first value in ttablename In others words, it's executed with the first table name obtained of the first row at the principal table.
Then, the question is: how I can execute the "sel" query with the different values stored in ttablename?
Thanks a lot for your advices, greetings!
--