#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0

    Unhappy Dynamic View in postgresql


    Hello All,

    I just wanted to confirm is it possible to create a dynamic view in postgresql.

    My requirement is i have two tables Table A and Table B
    now after few days Table B might be deleted so i want to have a view which displays the combine results of table A and B by checking whether table exists or not.

    So i want to have a view which executes sql as a string so that when i want to delete a table there will not be any dependency stopping me to do so.

    please let me know as this is a show stopper to me.

    If there is any other approach than having a view please let me know

    Thanks
    Siva Teja
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    You can't do this as a view because you wouldn't be able to drop any table referenced by the view unless you also drop the view - recreating the view afterwards wouldn't be possible as the table is no longer there.

    You can do this using a set returning function which then uses dynamic SQL based on the existance of the tables.

    You'd then use the function instead of a view:
    Code:
    select * from the_function();
    (you could hide the function call though by creating a view that uses that function)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    Thanks shammat for the quick response
    But if we go for function maintainence would be hectic right?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    Originally Posted by sivatejay
    But if we go for function maintainence would be hectic right?
    I have no idea what you mean with that.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    I mean in long run if we need to modify the function, it would take a detail study for another developer if the actual developer is changed.

    Is there any other approach suggested instead of using a function??
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    Originally Posted by sivatejay
    I mean in long run if we need to modify the function, it would take a detail study for another developer if the actual developer is changed.
    Which would be needed for the view just as well. If you have a complex requirement the code will always somehow reflect that complexity. If you want to hide the appearing/disapearing of a table (which sounds like a really awful idea in the first place) then this "magic" must happen somewhere, and someone must have understood how this "magic" was implemented.

    Is there any other approach suggested instead of using a function??
    Don't drop the tables used by the view?

    Putting this logic into a function is the best way to do it. It centralizes this "magic" in a single place and if the function is documented well, I don't see any reason why another developer should'nt understand it.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0

    Red face


    Originally Posted by shammat
    .
    Don't drop the tables used by the view?
    My requirement is such, tables will be dropped after few months..
    But until the table exists i need to query from that table

    So i thought of having a view instead, but view stops the table from dropping the table...

    And to my wonder QE team dont want to have procedures(functions).

    So i feel its better to query twice from java class instead
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    Originally Posted by sivatejay
    And to my wonder QE team dont want to have procedures(functions).
    Then they are stupid.

    Here are two blog posts that explain why they are indeed a very good idea:

    http://ledgersmbdev.blogspot.de/2012/07/one-advantage-of-logic-in-db-php.html

    http://ledgersmbdev.blogspot.de/2011/11/case-for-intelligent-databases.html

    http://ledgersmbdev.blogspot.de/2012/01/reply-to-tony-marstons-advocacy-of.html
    Last edited by shammat; August 27th, 2013 at 03:53 AM.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

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

IMN logo majestic logo threadwatch logo seochat tools logo