Re: indirect dereferencing a field in a record using plpgsql - Mailing list pgadmin-hackers

Hi,

I realized I still have a problem. Apparently there are issues with types
with regards
to the planner. I only discovered this when I tried accessing the NEW and
OLD records
in a trigger. The problem that arose was that the type that is being
dereferenced is not
necessarily what is expected when compared with the type that the planner
planned.

If anyone can give some insight to help make the functionality work - would
be appreciated.
I'll have to spend some more time figuring out the internals I guess. :)

Conrad

-----Original Message-----
From: pgadmin-hackers-owner@postgresql.org
[mailto:pgadmin-hackers-owner@postgresql.org]On Behalf Of Conrad
Vermeulen
Sent: Sunday, September 07, 2003 10:36 AM
To: pgadmin-hackers@postgresql.org
Subject: [pgadmin-hackers] indirect dereferencing a field in a record
using plpgsql


Hi,

I had a problem where I needed to indirectly dereference a field from a
record.

To illustrate:

CREATE FUNCTION test2() returns bool as '
DECLARE
    myrec record;
    fld text;

BEGIN
    select ''hello'' as a, ''world'' as b into myrec;

    fld = ''a''; -- the fieldname from myrec that we are interested in

    raise exception ''the result is "% %"'', myrec._fld, myrec.b;
    return true;
END;
' language 'plpgsql2';


Typically, dereferencing is done by specifying the fieldname that was
obtained either by a previous query
as illustrated above, or in predefined structures such as in the cases of
triggers with the NEW and OLD
records. The problem I had was that the functionality described above was
not available.

Requirements for such functionality is probably quite limited in general.
Most applications you would know
what columns you are dealing with, but the above requirement could add some
flexability.

I decided to hack the plpgsql language a little - for my testing I created a
seperate language 'plpgsql2'
which had my hack. The patch I made was on the pl_exec.c file on the
postgresql 7.3.2 source - the diff of
the original to my hack is listed below.

I'd appreciate any comments on the code as I've only spent a few hours
examining the postgres code to
make this possible. If there are any speedups, or potential reliability
problems, I'd like to know what they are.

Further, I noticed this was possible to do under pltcl, but plpgsql did not
support this. Would this be
something worth adding to plpgsql?

Thanks,

Conrad Vermeulen
conrad@fastforward.za.net

To briefly comment the extenstion I added:

Normal field dereferencing is done by 'record.field' where 'field' is part
of the 'record'. In
cases where 'indirect dereferencing' is required, the 'field' is actually a
variable which needs
to be dereferenced first. In order to differenciate between a normal record
field, and a global
field, my extension requires a '_' before the field name to identify that
this process is required.
The use of the '_' was because the parser tokenized other tokens such as $
or % and I did not want
to patch any other code.

# diff pl_exec.orig.c pl_exec.c
1737a1738,1772
>
>
> #define DYNAMDTYPE_RECFIELD
\
>       if (fno == SPI_ERROR_NOATTRIBUTE)
\
>
 \
>               int j;
\
>               char *fld;
\
>               if (recfield->fieldname[0] == '_')
\
>
 \
>                       fld= recfield->fieldname+1;
\
>                       for (j=0;j<estate->ndatums; j++)
\
>
 \
>                               var = (PLpgSQL_var *) (estate->datums[j]);
\
>                               if ((estate->datums[j]->dtype ==
PLPGSQL_DTYPE_VAR) &&                                       \
>                                       (!strcmp(fld,var->refname)))
\
>
 \
>                                       if (var->isnull )
\
>
 \
>                                               elog(ERROR, "\"%s\" found
but not assigned for use by \"%s\"", fld, rec->refname);     \
>                                               break;
\
>                                       }
\
>
fld=DatumGetCString(DirectFunctionCall1(textout,var->value));
\
>                                       fno = SPI_fnumber(rec->tupdesc,
fld);                                                \
>                                       pfree(fld);
\
>                                       if (fno != SPI_ERROR_NOATTRIBUTE)
\
>                                               break;
\
>                               }
\
>                       }
\
>               }
\
>               if (fno == SPI_ERROR_NOATTRIBUTE)
\
>                       elog(ERROR, "record \"%s\" has no field named
\"%s\"", rec->refname, recfield->fieldname);           \
>       }
>
>
>
1821,1822c1856
<                                               if (fno ==
SPI_ERROR_NOATTRIBUTE)
<                                                       elog(ERROR, "record
\"%s\" has no field named \"%s\"", rec->refname, recfield->fieldname);
---
>                                               DYNAMDTYPE_RECFIELD
1997,1998c2031
<                               if (fno == SPI_ERROR_NOATTRIBUTE)
<                                       elog(ERROR, "record \"%s\" has no
field named \"%s\"", rec->refname, recfield->fieldname);
---
>                               DYNAMDTYPE_RECFIELD
2083,2085c2116
<                               if (fno == SPI_ERROR_NOATTRIBUTE)
<                                       elog(ERROR, "record \"%s\" has no
field named \"%s\"", rec->refname, recfield->fieldname);
<
---
>                               DYNAMDTYPE_RECFIELD
2644,2645c2675
<                               if (fno == SPI_ERROR_NOATTRIBUTE)
<                                       elog(ERROR, "record \"%s\" has no
field named \"%s\"", rec->refname, recfield->fieldname);
---
>                               DYNAMDTYPE_RECFIELD
2938,2939c2968
<                       if (fno == SPI_ERROR_NOATTRIBUTE)
<                               elog(ERROR, "record \"%s\" has no field
named \"%s\"", rec->refname, recfield->fieldname);
---
>                       DYNAMDTYPE_RECFIELD
3132,3133c3161
<                               if (fno == SPI_ERROR_NOATTRIBUTE)
<                                       elog(ERROR, "record \"%s\" has no
field named \"%s\"", rec->refname, recfield->fieldname);
---
>                               DYNAMDTYPE_RECFIELD
3270,3271c3298
<                               if (fno == SPI_ERROR_NOATTRIBUTE)
<                                       elog(ERROR, "record \"%s\" has no
field named \"%s\"", rec->refname, recfield->fieldname);
---
>                               DYNAMDTYPE_RECFIELD



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.509 / Virus Database: 306 - Release Date: 8/12/2003