Thread: 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
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
Conrad, this is the wrong list for your question, use pgsql-hackers instead. Usually, I'd implement your indirect dereferencing using a runtime compiled query string, as the doc show it. Regards, Andreas
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