josep porres wrote:
I have a given table structure, so redesign it now is not possible due to
having change a lot of things
Furthermore, using M3TRAM INTEGER[5], PREU NUMERIC(10,2)[5]
seems to me a very good way but I think it may appear problems when
accessing to that table
from third party apps such as excel, odbc, ... isn't it?
I don't know, but I would not be too surprised if that could be an
issue. I avoid array types myself except in "private" parts of the
database that're only exposed to apps indirectly via views or stored
procedures. In fact, I really only use them in stored procedures and
rarely then.
So the simplest way could be the most suitable one.
However, imagine I had more fields....
That's why I suggested using a secondary table. Adding fields won't be
fun the way you're doing things.
Is not really possible to 'calculate' a string, that is the field name, yeah
like it was an array,
and reference a field in a row using that string?
something like this
s:='PREU1';
row_tfa.s := x;
It can probably be done using PL/PgSQL's EXECUTE statement. In
PostgreSQL 8.3 this supports the INTO clause (you had to use some
less-than-pretty workarounds in previous versions) so you can write
something like:
FOR IN 1..5 LOOP
EXECUTE 'SELECT row'||rownum||'FROM blah'
INTO STRICT result_variable[i];
END LOOP
However, as far as I know you cannot access the value of local variables
in EXECUTEd SQL. So if you've DECLARE'd a variable that you're storing a
row in, you won't be able to generate a query that can access arbitrary
columns of it. You can go and SELECT the original row again, but this
will of course get slow (10 queries per row the way you're doing it) and
it's ugly.
It's also important to understand that EXECUTEd queries are re-planned
every time they're run. That makes them expensive relative to normal
assignments, SELECT INTO, etc in PL/PgSQL.
I guess you could write an EXECUTE query that assembled an array
literal. Consider the following example:
BEGIN;
CREATE TABLE fiveints(
a INTEGER,
b INTEGER,
c INTEGER,
d INTEGER,
e INTEGER
) WITH(OIDS=FALSE);
INSERT INTO fiveints (a,b,c,d,e) VALUES (1,2,3,4,5);
CREATE OR REPLACE FUNCTION testfn() RETURNS integer[5] AS $$
DECLARE
arr INTEGER[5];
BEGIN
EXECUTE 'SELECT ARRAY[a,b,c,d,e] FROM fiveints' INTO STRICT arr;
RETURN arr;
END;
$$ LANGUAGE 'plpgsql';
SELECT testfn();
... which when executed outputs:
testfn
-------------
{1,2,3,4,5}
(1 row)
Note the use of array constructor syntax.
The version for handling multiple values would be:
BEGIN;
CREATE TABLE fiveints(
a INTEGER,
b INTEGER,
c INTEGER,
d INTEGER,
e INTEGER
) WITH(OIDS=FALSE);
INSERT INTO fiveints (a,b,c,d,e) VALUES (1,2,3,4,5);
INSERT INTO fiveints (a,b,c,d,e) VALUES (11,21,31,41,51);
CREATE OR REPLACE FUNCTION testfn() RETURNS setof integer[5] AS $$
DECLARE
arr INTEGER[5];
BEGIN
FOR arr IN EXECUTE 'SELECT ARRAY[a,b,c,d,e] FROM fiveints' LOOP
RETURN NEXT arr;
END LOOP;
RETURN;
END;
$$ LANGUAGE 'plpgsql';
SELECT * FROM testfn();
I think you're trying to swim upstream here, personally, and do
something very much the hard way, but it sounds like you're stuck with
existing apps with inflexible designs that you need to accommodate. Even
then, maybe you can use some stored procedures and updateable views to
provide the old interface for those apps, while internally changing the
database's structure to something a bit nicer to work with.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
