Thread: An interisting conundrum where tables have a column called "found"

I am putting together a DB that records information about a set of web
sites and how they link to one another. As one site refers to another, I
monitor the first site and then record when I find the referred site.

I have a table called sa_site like this:
ensa1.1: sa_site  Field   Type Not Null Default

site_id
bigint
NOT NULL



host_uri
character
varying(1024)
NOT NULL



found
timestamp
with time
zone
NOT NULL

I also have a function called add_site that adds the newly found site.

So far so good.
To test my code I wrote the INSERT statement by hand:
insert into sa_site (site_id, found, host_uri) values
(nextval('sa_site_id_seq'), 'now', 'www.endoid.net');

and everything worked fine when called from psql.

Then I added the code to my add_site function and got the following
error:
ensa1.1=> select add_site('www.endoid.net', 4, null );
WARNING:  Error occurred while executing PL/pgSQL function add_site
WARNING:  line 26 at SQL statement
ERROR:  parser: parse error at or near "$1" at character 43

I looked and looked but couldn't find anything that could explain the
error. Then, being somewhat used to Oracle I tried renaming the "found"
column to "found_on". Oracle occasionally has discrepencies in its rules
for the naming of objects, so I thought that something *similar* might
be happening with PG. Anyways this change did work in my PL/pgSQL
function.

Could you guys figure out where a general description of "please don't
use keywords as column names even if you're allowed to at create time
because something somewhere will throw an unintellligable error" should
live on the site?

Hope this is of help,

/e

> 
> I am putting together a DB that records information about a set of web
> sites and how they link to one another. As one site refers to another, I
> monitor the first site and then record when I find the referred site.
> 
> [snip]
> 
> I also have a function called add_site that adds the newly found site.
> 
> So far so good.
> To test my code I wrote the INSERT statement by hand:
> insert into sa_site (site_id, found, host_uri) values
> (nextval('sa_site_id_seq'), 'now', 'www.endoid.net');
> 
> and everything worked fine when called from psql.
> 
> Then I added the code to my add_site function and got the following
> error:
> ensa1.1=> select add_site('www.endoid.net', 4, null );
> WARNING:  Error occurred while executing PL/pgSQL function add_site
> WARNING:  line 26 at SQL statement
> ERROR:  parser: parse error at or near "$1" at character 43
> 
> I looked and looked but couldn't find anything that could explain the
> error. Then, being somewhat used to Oracle I tried renaming the "found"
> column to "found_on". Oracle occasionally has discrepencies in its rules
> for the naming of objects, so I thought that something *similar* might
> be happening with PG. Anyways this change did work in my PL/pgSQL
> function.
> 
> Could you guys figure out where a general description of "please don't
> use keywords as column names even if you're allowed to at create time
> because something somewhere will throw an unintellligable error" should
> live on the site?
> 
There is a SQL Key Words section, and I remember when porting to 
postgres I saw complaints about a column named 'offset'. 
So I assume there is a key word checking function already in operation. 
Maybe it simply needs an update. 
Regards, Christoph