Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2016
    Posts
    4

    Question Answered: compile function with reference to table that does not exist

    Hi all,

    I'm new to PostgreSQL, but have 8+ years of experience in Oracle PL/SQL.

    I've read in article from 2012 year that it was possible to compile function with reference to table that does not exist.
    I just tried to do it in my PostgreSQL 9.3 and got error
    Code:
    ERROR:  relation "table_name" does not exist
    If it was possible at all, then I got few questions:
    What was the last version where it was possible to compile function with reference to table that does not exist?
    How did developers deal with it untill it was fixed?

    Thanks ahead.

  2. Best Answer
    Posted by shammat

    "This is possible with PL/pgSQL functions as they do not check the SQL when you create the function, e.g:
    Code:
    create function will_fail_at_runtime() 
      returns integer
    as
    $$
    declare 
      l_count integer;
    begin
      select count(*) into l_count 
      from some_non_existing_table;
      return l_count;
    end;
    $$
    language plpgsql;

    When you use (plain) SQL functions, the SQL is validated when you create it:
    Code:
    create function will_fail_at_creation() 
      returns integer
    as
    $$
      select count(*)
      from some_non_existing_table;
    $$
    language sql;
    "


  3. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    This is possible with PL/pgSQL functions as they do not check the SQL when you create the function, e.g:
    Code:
    create function will_fail_at_runtime() 
      returns integer
    as
    $$
    declare 
      l_count integer;
    begin
      select count(*) into l_count 
      from some_non_existing_table;
      return l_count;
    end;
    $$
    language plpgsql;

    When you use (plain) SQL functions, the SQL is validated when you create it:
    Code:
    create function will_fail_at_creation() 
      returns integer
    as
    $$
      select count(*)
      from some_non_existing_table;
    $$
    language sql;
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #3
    Join Date
    Aug 2016
    Posts
    4

    Smile

    Got it, thanks shammat!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •