Thread: 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@agliodbs.com
Aglio Database Solutions
San Francisco

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
finallyfigured 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
theceiling and start all over again and thus not making the oid a unique identifier. But we know that for our purpose
thiswill 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

Niclas,

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

Well, it's a truism of SQL scripting languages in general that variables can
substitute for constants but not for objects.   So I guess that people
assumed that it didn't need to be stated -- like many other things that trip
up newbies <grin>.  I've *got* to get that faq engine up and running ...

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

Sorry.  Didn't check for typos.

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

OIDs are actually a decent unique identifier for tables (but not for rows).
However, you'll find that the usefulness of that OID in postgres is limited;
pg won't, for example, accept the OID in place of the table name.  But if
your middleware needs a numerical table id -- why not?   The info you need is
in the pg_class table; see the online docs about system catelogs.

The one thing I'll caution you about is that those OIDs will change on
backup/restore of your database.   So your app should be prepared to reload
all of the oid-name correspondences on database reload.

--
Josh Berkus
Aglio Database Solutions
San Francisco

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 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
finallyfigured out that one has to make two small changes to make it work (and that 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
theceiling and start all over again and thus not making the oid a unique identifier. But we know that for our purpose
thiswill 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

Hello, i've installed postgres in my mandrake linuxbox.
when i try to start it, it responds:

bash-2.05b$ /etc/init.d/postgresql start
Starting postgresql service: standard in must be a tty


What can i do?
Thanks

...
***********************************************************************
Vittorio Zuccala'
ICQ#20318568
YMess: nathanvit