I've several list of items that have to be rendered on a web apps in
the same way.
The structure is:
create table items (
itemid int primary key,
/* some fields */
);
create table headert1 (
ht1 int primary key,
/* several other fields that varies in nature */
);
create table itemlistt1 (
ht1 int references headert1 ht1,
itemid references items (itemid)
);
The query always match this pattern:
select i.fieldA, i.fieldB, ..., from itemlistt1 il
join items i on i.itemid=il.itemid
where il.ht1=[somevalue];
the nature of the lists and their usage pattern is very different.
So unless someone come up with a better design I still would like to
keep the item lists in different tables.
I'd like to build up a function that takes the name of the table and
the key to dynamically build up the query... but I don't know what
should I use to sanitize them.
create or replace function getitemlist(listtable text, listkey text,
keyvalue int,
, out ....) rerurns setof records as
$$
declare
statement text;
begin
statement:='select i.fieldA, i.fieldB, ..., from ' ||
escapefunc1(listtable) ||
' il join items i on i.itemid=il.itemid ' ||
' where il.' || escapefunc2(listtable) || '=' || keyvalue;
return query execute statement; // can I?
is it quote_ident the right candidate for escapefuncN?
But this is still at risk of misuse... (eg. passing ('items',
'fieldA', 1) may return something that was not meant to be seen.
One way would be to build up a table of permitted (table, key) and
then just pass the table and the key value.
What should be the equivalent of quote_ident in PHP?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
