Re: get the oid

Hi Josh,

Thanks a lot for your help!

At 07.07 -0800 03-03-10, Josh Berkus wrote:
> > 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?

Yup, and now finding myself standing up to my knees in the sql-mud! :-)

> > 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.

Ah, that's a very good piont! Thanks.

>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.

OK, I was beginning to suspect that but I couldn't find that stated, explicitly or imlicitly, anywhere in the documentation.

>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).

I had problems running this at first (the error messages that you get in sql isn't that enlightening always...). But I finally figured out that one has to make two small changes to make it work (which was a good exercise :-):

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 execute query_string loop
ret := rec_table.oid;
end loop;
return ret;
end;'
language 'plpgsql' with (isstrict);

>... 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?

Well we thought that the oid was a good way to refer to tables uniquely, tables that we don't know the name of initially. I know that one is explicitly discouraged to use oid's as unique identifiers as the four byte number can hit the ceiling and start all over again and thus not making the oid a unique identifier. But we know that for our purpose this will not happen anyway as the number of tables we are handling in this context is limited. Is that a bad idea?

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

Thanks, nice thing to know! :-)

Cheers,
Niclas