Re: get the oid

Nick,

> I'm not only a novice to pgsql but also to sql in general (as well as
> new in this list)...

Jumping in with both feet, are you?

> Here's my question: Why doesn't the following function work?
>
> create function getoid(name) returns integer as '
> declare
> ret integer;
> tablename alias for $1;
> begin
> select into ret oid from tablename;
> return ret;
> end;'
> language 'plpgsql';

For two reasons:

1) The above query, if you fixed it, would return the OID for the first record
of the table, not the OID for the table. If you want the OID for the table,
query the pg_class system table.

2) If you want to pass a dynamic query string in PL/pgSQL, you need to use a
RECORD loop and pass the query as a string. PL/pgSQL does *not* permit
passing variables as table, column, or other object names ... variables can
only substitute for constants. Thus to get the above to work:

create function getoid(name) returns integer as '
declare
ret integer;
tablename alias for $1;
query_string TEXT;
rec_table RECORD;
begin
query_string := 'SELECT oid FROM ' || tablename;
FOR rec_table IN query_string LOOP
ret := rec_table.oid;
END LOOP;
RETURN ret;
end;'
language 'plpgsql' WITH (ISSTRICT);
(above is 7.2.x syntax).

... though, as I said, this will just get you the OID of the first row of the
table, not the OID of the table itself. Why doyou want the OID, anyway?

Oh, and ISSTRICT is so that the function will return a NULL automatically
whenever NULL Is passed as the tablename.

--
Josh Berkus
josh(at)agliodbs(dot)com
Aglio Database Solutions
San Francisco