Foreign Keys

Michael Davis wrote:

What is the possibility of recording foreign keys (a partial foreign key
implementation) in the database such that the ODBC interface can correctly
report them to client applications? This would require an enhancement to
Postgres and to the ODBC driver for Postgres. I have a problem with Access
97 not working properly with forms that contain sub forms representing
parent/child or master/detail tables. For example: orders and orderlines.
I think this is caused by the lack of foreign key support. I am basing this

The driver does support SQLForeignKeys. It does say it supports this function
in SQLFunctions.

However, the current implementation is based on the SPI/trigger stuff from the
pg_trigger table. If you don't have any trigger info, then SQLForeignKeys will
not report anything. This is most likely the problem.

Byron

Excellent! I will start working on it.

-----Original Message-----
From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
Sent: Thursday, February 25, 1999 8:38 AM
To: Michael Davis
Cc: 'pgsql-interfaces@postgreSQL.org'; pgsql postgres;
pgsql-hackers@postgreSQL.org
Subject: Re: [INTERFACES] Foreign Keys

Michael Davis wrote:

What is the possibility of recording foreign keys (a partial

foreign key

implementation) in the database such that the ODBC interface can

correctly

report them to client applications? This would require an

enhancement to

Postgres and to the ODBC driver for Postgres. I have a problem

with Access

97 not working properly with forms that contain sub forms

representing

parent/child or master/detail tables. For example: orders and

orderlines.

I think this is caused by the lack of foreign key support. I am

basing this

The driver does support SQLForeignKeys. It does say it supports
this function
in SQLFunctions.

However, the current implementation is based on the SPI/trigger
stuff from the
pg_trigger table. If you don't have any trigger info, then
SQLForeignKeys will
not report anything. This is most likely the problem.

Byron

The second problem is this:

conn=153237224, query='SELECT "RentalOrders"."rentalorderlinesid" FROM "rentalorderlines"
"RentalOrders" WHERE ("rentalorderid" = NULL ) '
ERROR from backend during send_query: 'ERROR: parser: parse error at or near "null"'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'

Since postgres will not recognize the syntax (where 'col' = null)... it only recognizes
"isnull". I was hoping someone would have added the ability for the parser to handle this at
some point (Hey Dave, maybe you could contribute something here man :-).

Byron

I do not poke my nose into odbc as I have nothing to do with it, but
this parsing problem caught my attention. To me, 'isnull' and '= null'
are the same token. So I fixed the aforementioned problem like this:

1. In backend/parser.scan.l, find the line that reads:

identifier {letter}{letter_or_digit}*

and put this following macro after it:

isnull ={space}*(null|NULL)

it does not matter where before the beginning of the rules section you
will put it, but it is better to keep related things close to each other.

2. In the same file, find the line that reads:

{identifier} {

and insert the following rule before it

{isnull} {
int i;
ScanKeyword *keyword;

for(i = 0; yytext[i]; i++)
if (isascii((unsigned char)yytext[i]) &&
isupper(yytext[i]))
yytext[i] = tolower(yytext[i]);
if (i >= NAMEDATALEN)
yytext[NAMEDATALEN-1] = '\0';

keyword = ScanKeywordLookup((char*)"isnull");
return keyword->value;
}

3. run make && make install in the src directory, then stop and restart postmaster

I understand it is an ugly hack but if you are desperate to get things running ...
If you don't try to use it as NULL = 'col', you should be OK.

--Gene

| NULL_P '=' a_expr
{ $$ = makeA_Expr(ISNULL, NULL, $3, NULL); }

This leads to a shift/reduce conflict in yacc.

What's wrong with shift/reduce conflicts?

yacc looks ahead only one token to determine the parsing possibilities,
and will maintain multiple, parallel possibilities until it is able to
resolve them (keeping in mind this one-token constraint). With a
"shift/reduce" conflict, at least one path will *never* be possible,
even though you thought it should be from the grammar. So you will end
up with some language feature permanently unavailable. And worse, when a
new conflict is introduced it might be the older feature which is
trashed :(

- Tom