Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Identical table name and function name in PostgreSQL 11.1
CREATE OR REPLACE FUNCTION element_pathname_insert() RETURNS TRIGGER AS $$
BEGIN
RAISE WARNING 'element_pathname_insert says: %', NEW.id;
INSERT INTO element_pathname (element_id, pathname)
VALUES (NEW.id, element_pathname(NEW.id));
RETURN NEW;
END
$$ LANGUAGE 'plpgsql';
From the definition of the table: element
element_pathname_insert AFTER INSERT ON element FOR EACH ROW EXECUTE PROCEDURE element_pathname_insert()
@dlangille

This comment has been minimized.

Copy link
Owner Author

commented Nov 29, 2018

The problem? element_pathname(NEW.id) is returning an empty string, which is what it will do if it cannot find an entry for NEW.id

@dlangille

This comment has been minimized.

Copy link
Owner Author

commented Nov 29, 2018

element_pathname() is declared as stable

@dlangille

This comment has been minimized.

Copy link
Owner Author

commented Nov 29, 2018

The error is:

freshports.dev=# select Element_Add('/base/releng/12.0/release/tools/gce.conf', 'F');
WARNING:  looping for base/releng/12.0/release/tools/gce.conf
WARNING:  looping for releng/12.0/release/tools/gce.conf
WARNING:  looping for 12.0/release/tools/gce.conf
WARNING:  looping for release/tools/gce.conf
WARNING:  looping for tools/gce.conf
WARNING:  inserting tools
WARNING:  element_pathname_insert says: 910902
ERROR:  duplicate key value violates unique constraint "element_pathname_pathname"
DETAIL:  Key (pathname)=() already exists.
CONTEXT:  SQL statement "INSERT INTO element_pathname (element_id, pathname)
         VALUES (NEW.id, element_pathname(NEW.id))"
PL/pgSQL function element_pathname_insert() line 4 at SQL statement
SQL statement "insert into element(id, parent_id, directory_file_flag, name, status)
                            values(element_id, element_parent_id, 'D', element_name, 'A')"
PL/pgSQL function element_add(text,character) line 92 at SQL statement
freshports.dev=# 

@dlangille

This comment has been minimized.

Copy link
Owner Author

commented Nov 29, 2018

The function is:

CREATE OR REPLACE FUNCTION element_pathname (integer, boolean) RETURNS text
    AS $$
   DECLARE
      element_id    ALIAS FOR $1;
      leading_slash ALIAS for $2;
      my_parent_id  int4;
      element_name  text;
      pathname      text;

begin
  RAISE WARNING ' element_pathname invoked with %', element_id;
  pathname = '';
  select name, parent_id
    into element_name, my_parent_id
    from element
   where id = element_id;

  IF FOUND THEN
    pathname := '/' || element_name || pathname;
    WHILE FOUND LOOP
      select name, parent_id
        into element_name, my_parent_id
        from element
       where id = my_parent_id;

      IF FOUND THEN
         IF my_parent_id IS NULL THEN
           IF leading_slash THEN
              pathname = '/' || element_name || pathname;
           ELSE
              pathname = element_name || pathname;
           END IF;
           EXIT;
         ELSE
           pathname = '/' || element_name || pathname;
         END IF;
      END IF;
    END LOOP;
  END IF;

  return pathname;
END;
$$
    LANGUAGE plpgsql STABLE;

@dlangille

This comment has been minimized.

Copy link
Owner Author

commented Nov 29, 2018

The duplicate key issue is a red herring, that only occurs because the previous transaction retrieve a '' for element_pathname () and inserted it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.