Пользовательские функции Postgres: самостоятельные соединения в SQL

У меня есть следующий SQL, который прекрасно работает в PSQL:

SELECT
   parent.relname, child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='table_name';

Проблема в том, что когда я помещаю это в функциональный блок create:

CREATE OR REPLACE FUNCTION my_function(tablename text)
RETURNS SETOF RECORD AS
$$


  SELECT
   parent.relname, child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname= tablename;



$$ LANGUAGE plpgsql;

Я получаю ошибку:

ERROR:  schema "parent" does not exist
LINE 10:     parent.relname

,

Когда я пробовал "public.parent.relname", я получаю ошибку:

ERROR:  cross-database references are not implemented: 

    public.parent.relname
    LINE 10:     public.parent.relname

Как мне правильно справиться с этой ситуацией, что не так с моим синтаксисом?

Источник

2 ответа

Решение

Я вижу несколько ошибок в приведенном вами примере. Я взял твой пример, и несколько вещей выпрыгнули, выпиши:

  • Тип возврата не объявлен (это может быть запись, таблица, пользовательский тип,...)
  • Имя языка не объявлено (plpgsql, sql, c,...)
  • ...

Посмотрите документацию, там вы можете найти больше информации о том, как создать функцию в PostgreSQL, а также есть много примеров (google, stackru, сообщения в блогах).

Рабочий пример, который работал для меня, на основе вашего примера (также в зависимости от типа возвращаемого значения, имени языка):

CREATE OR REPLACE FUNCTION my_function()
RETURNS SETOF RECORD AS 
$$ 
SELECT
    parent.relname, child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='table_name';
$$ LANGUAGE sql;
Источник

Вот что сработало для меня:

CREATE OR REPLACE FUNCTION my_function (tablename text)
RETURNS TEXT AS
$$
DECLARE
  ret text := '';

BEGIN

SELECT
    parent.relname, child.relname

FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname= tablename;



RETURN ret;


END;


$$ LANGUAGE plpgsql;

поскольку язык должен был быть "plpgsql", необходим блок BEGIN/END

Источник
Другие вопросы по тегам