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. Building up a query string and then using the 'EXECUTE'
functionality also does not work as I think a new context is created and the
'NEW' and 'OLD' records are not available - or they are part of the plpgsql
language and not available via the methods when calling EXECUTE.

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 noticed this was possible to
do under pltcl, but plpgsql did not support this. Would this be something
worth adding to plpgsql?

Anyways, 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 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.  The problem I faced was in
the function exec_stmt_execsql():

	if (expr->plan_argtypes[i] != SPI_gettypeid(rec->tupdesc, fno))
      	elog(ERROR, "type of %s.%s doesn't match that when preparing the
plan", rec->refname, recfield->fieldname);

I have to spend some more time understanding the exact ADT that is created
by the parser. Possibly my quick hack may not work if the parser
predetermines the type  as the functionality required would only really be
able to determine the type at time of execution.

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. :)

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



Mon, 8 Sep 2003 11:27:30 +0200
"Conrad Vermeulen" <conrad@fastforward.za.net>
FW: indirect dereferencing a field in a record using plpgsql
"Conrad Vermeulen" <conrad@fastforward.za.net> writes:
> Possibly my quick hack may not work if the parser
> predetermines the type  as the functionality required would only really be
> able to determine the type at time of execution.

The problem is that the first time through, the execution plan for the
command or expression is generated and cached.  So you can't change the
type of the field dereference later.

plpgsql is really not designed for this sort of thing; it's designed to
be fast with predictable data types.  Use pltcl or plperl instead.
There's a reason why we support multiple PLs ;-)

I agree that it would be good to fix EXECUTE so that one could refer to
NEW or OLD in the constructed query (and other plpgsql variables too).
I believe this would take a rather serious overhaul of plpgsql's parser
though ...

			regards, tom lane

Mon, 08 Sep 2003 10:31:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: FW: indirect dereferencing a field in a record using plpgsql 