Thread:

Hello,

I am new to postgresql and new to this list so please excuse any
obvious blunders.

I am currently trying to convert an ingres database into a postgres database.
While trying to implement a procedure I have come across the two
following problems.

1.
  In Ingres I can check if the ingres superuser is the current userby using

      WHERE dbmsinfo('username') = dbmsinfo('dba')

  I can't find out how to do the equivalent in postgres. Can anyone help?

  The special function
      current_user
  will give me the current user but I can't find a function to give me
  the postgresql superuser (typically 'postgres'). I tried getpgusername()
  but it returns the current user.

2.
  I want to run 2 procedures from a single trigger. However, I believe
  that only one is permitted. Therefore the trigger needs to execute a
  procedure which in turn executes the 2 procedures.

  When I implement this and then attempt to insert a line into the table
  I get the following
      psql:ex.junk:91: NOTICE:  FUNCTION BOTH - start
      psql:ex.junk:91: ERROR:  parser: parse error at or near "f_one"

  How can I get this to work?


  eg.

  CREATE FUNCTION f_one()
    RETURNS OPAQUE AS
    '
      DECLARE

      BEGIN
        RAISE NOTICE ''FUNCTION 1'';

        IF TG_OP = ''DELETE'' THEN
          RETURN OLD;
        ELSE
          RETURN NEW;  -- for UPDATE and INSERT operations
        END IF;
      END;
    '
    LANGUAGE 'plpgsql';


  CREATE FUNCTION f_both()
    RETURNS OPAQUE AS
    '
      DECLARE
      BEGIN
        RAISE NOTICE ''FUNCTION BOTH - start '';

        EXECUTE PROCEDURE f_one();
        RAISE NOTICE ''FUNCTION BOTH -done'';
        IF TG_OP = ''DELETE'' THEN
          RETURN OLD;
        ELSE
          RETURN NEW;  -- for UPDATE and INSERT operations
        END IF;
      END;
    '
    LANGUAGE 'plpgsql';


  CREATE TRIGGER t_personnel_udi
    BEFORE INSERT OR UPDATE OR DELETE
    ON personnel
    FOR EACH ROW
      EXECUTE PROCEDURE f_both();


Many Thanks
Maggie

On Thu, Jan 17, 2002 at 06:59:18PM +0000, M.L.Bowman wrote:
>   In Ingres I can check if the ingres superuser is the current userby using
>
>       WHERE dbmsinfo('username') = dbmsinfo('dba')
>
>   I can't find out how to do the equivalent in postgres. Can anyone help?

        WHERE current_user=pg_get_userbyid(1)


>         EXECUTE PROCEDURE f_one();
         PERFORM  f_one();

Good luck !

--
Holger Krug
hkrug@rationalizer.com

At 06:59 PM 1/17/02 +0000, M.L.Bowman wrote:
>  In Ingres I can check if the ingres superuser is the current userby using
>      WHERE dbmsinfo('username') = dbmsinfo('dba')
>  I can't find out how to do the equivalent in postgres. Can anyone help?

fbax=# select usename,usesuper from pg_user where usename = current_user;
 usename | usesuper
---------+----------
 fbax    | t
(1 row)