Re: porting vb6 code to pgplsql, referencing fields - Mailing list pgsql-general

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