Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit98ad3fc

Browse files
committed
Hi All,
I've changed the check_primary_key() function code to allow for eitherthe "automatic insert key rule" or "dependent insert key rule".Previously it restricted the addtion of a child entry if thecorresponding parent entry was not there. Now if the option is"automatic" it will add an entry in the parent too ( it will besuccessful if there are no no-null fields in the parent apart from theprimary key).The way to use it now is::/* * check_primary_key () -- check that key in tuple beinginserted/updated * references existing tuple in "primary" table. * Though it's called without args You have to specify referenced * table/keys while creating trigger: key field names in triggeredtable, * referenced table name, referenced key field names,type of action[automatic|dependent]: * EXECUTE PROCEDURE * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2','[automatic|dependent]'). */I am attaching the new ../contrib/spi/refint.c file which will do this.I will be glad to help in case of any problems.- Anand.
1 parentdb42533 commit98ad3fc

File tree

1 file changed

+65
-7
lines changed

1 file changed

+65
-7
lines changed

‎contrib/spi/refint.c

Lines changed: 65 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -30,9 +30,9 @@ static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans);
3030
* references existing tuple in "primary" table.
3131
* Though it's called without args You have to specify referenced
3232
* table/keys while creating trigger: key field names in triggered table,
33-
* referenced table name, referenced key field names:
33+
* referenced table name, referenced key field names,type of action [automatic|dependent]:
3434
* EXECUTE PROCEDURE
35-
* check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2').
35+
* check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2','[automatic|dependent]').
3636
*/
3737

3838
HeapTuple/* have to return HeapTuple to Executor */
@@ -41,9 +41,10 @@ check_primary_key()
4141
Trigger*trigger;/* to get trigger name */
4242
intnargs;/* # of args specified in CREATE TRIGGER */
4343
char**args;/* arguments: column names and table name */
44-
intnkeys;/* # of key columns (= nargs / 2) */
44+
intnkeys;/* # of key columns (=(nargs-1) / 2) */
4545
Datum*kvals;/* key values */
4646
char*relname;/* referenced relation name */
47+
char*action;/* action on insert or update*/
4748
Relationrel;/* triggered relation */
4849
HeapTupletuple=NULL;/* tuple to return */
4950
TupleDesctupdesc;/* tuple description */
@@ -84,10 +85,14 @@ check_primary_key()
8485
nargs=trigger->tgnargs;
8586
args=trigger->tgargs;
8687

87-
if (nargs %2!=1)/* odd number of arguments! */
88-
elog(ERROR,"check_primary_key:odd number of arguments should be specified");
88+
if ((nargs-1) %2!=1)/* odd number of arguments! */
89+
elog(ERROR,"check_primary_key:even number of arguments should be specified");
8990

90-
nkeys=nargs /2;
91+
nkeys= (nargs-1) /2;
92+
action=args[nargs-1];
93+
if (strcmp(action,"automatic")&&strcmp(action,"dependent"))
94+
elog(ERROR,"check_primary_key: unknown action");
95+
nargs=nargs-1;
9196
relname=args[nkeys];
9297
rel=CurrentTriggerData->tg_relation;
9398
tupdesc=rel->rd_att;
@@ -198,9 +203,62 @@ check_primary_key()
198203
/*
199204
* If there are no tuples returned by SELECT then ...
200205
*/
201-
if (SPI_processed==0)
206+
if (SPI_processed==0&&strcmp(action,"dependent")==0)
202207
elog(ERROR,"%s: tuple references non-existing key in %s",
203208
trigger->tgname,relname);
209+
elseif (strcmp(action,"automatic")==0)
210+
{
211+
/* insert tuple in parent with only primary keys */
212+
/* prepare plan */
213+
void*pplan;
214+
charsql[8192];
215+
216+
/*
217+
* Construct query:INSERT INTO relname (Pkey1[,Pkey2]*) values ($1,$2..);
218+
*/
219+
sprintf(sql,"insert into %s ( ",relname);
220+
for (i=0;i<nkeys;i++)
221+
{
222+
sprintf(sql+strlen(sql),"%s%s ",args[i+nkeys+1],(i<nkeys-1) ?",":"");
223+
}
224+
sprintf(sql+strlen(sql),") values (");
225+
for (i=0;i<nkeys;i++)
226+
{
227+
sprintf(sql+strlen(sql),"$%d%s ",i+1,(i<nkeys-1) ?",":"");
228+
}
229+
sprintf(sql+strlen(sql),")");
230+
231+
/* Prepare plan for query */
232+
pplan=SPI_prepare(sql,nkeys,argtypes);
233+
if (pplan==NULL)
234+
elog(ERROR,"check_primary_key: SPI_prepare returned %d",SPI_result);
235+
236+
/*
237+
* Remember that SPI_prepare places plan in current memory context
238+
* - so, we have to save plan in Top memory context for latter
239+
* use.
240+
*/
241+
pplan=SPI_saveplan(pplan);
242+
if (pplan==NULL)
243+
elog(ERROR,"check_primary_key: SPI_saveplan returned %d",SPI_result);
244+
plan->splan= (void**)malloc(sizeof(void*));
245+
*(plan->splan)=pplan;
246+
plan->nplans=1;
247+
/*
248+
* Ok, execute prepared plan.
249+
*/
250+
ret=SPI_execp(*(plan->splan),kvals,NULL,1);
251+
/* we have no NULLs - so we pass ^^^^ here */
252+
253+
if (ret<0)
254+
elog(ERROR,"check_primary_key: SPI_execp returned %d",ret);
255+
256+
/*
257+
* If there are no tuples returned by INSERT then ...
258+
*/
259+
if (SPI_processed==0)
260+
elog(ERROR,"error: can't enter automatically in %s",relname);
261+
}
204262

205263
SPI_finish();
206264

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp