Re: How to keep format of views source code as entered?

Hi Ingolf, > On 26. Jan, 2021, at 14:41, Markhof, Ingolf > wrote: > > You may not be able to delete tables / views that are referenced by other > users objects, e.g. views. Unless you add the CASCADE option which will cause > all depending views to be deleted as well. And the CASCASE will

Re: How to keep format of views source code as entered?

Hi! Today, I made an astonishing / disappointing experience related to that source code topic: You may not be able to delete tables / views that are referenced by other users objects, e.g. views. Unless you add the CASCADE option which will cause all depending views to be deleted as well.

Re: How to keep format of views source code as entered?

Hi Laurenz, > On 14. Jan, 2021, at 04:59, Laurenz Albe wrote: > > If PostgreSQL were to store the original text, either that text would become > wrong, or you would have to forbid renaming of anything that is referenced > by a view. this is why views, procedures, functions and packages can

Re: How to keep format of views source code as entered?

On Wed, Jan 13, 2021 at 6:40 PM Adam Brusselback wrote: > It has been a major annoyance for views with complex subqueries or where > clauses, the PG representation is absolutely unreadable. > The path to a solution here is to write a schema-to-text system that presents the derived output in a

Re: How to keep format of views source code as entered?

On Wed, 2021-01-13 at 20:39 -0500, Adam Brusselback wrote: > > Admittedly, the system probably should be made to save the text, should > > someone wish to write such a patch. > > It has been a major annoyance for views with complex subqueries or where > clauses, the PG representation is

Re: How to keep format of views source code as entered?

> Admittedly, the system probably should be made to save the text, should someone wish to write such a patch. Just wanted to throw $0.02 behind this idea if anyone does want to take it up later. Using a source control system is better obviously. But even if you use source control it is still

Re: How to keep format of views source code as entered?

Ugh, I wasn't and just tried it, thanks. I've saved comments before on pgadmin and wasn't aware it using a sql statement to save it, I thought it was local to my environment. Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Tuesday, January 12, 2021 4:17 PM, Alban

Re: How to keep format of views source code as entered?

> On 12 Jan 2021, at 20:54, Alex Williams wrote: > > Hi Ingolf, > > For comments in views, I create a unused CTE and put my comments there, e.g. > > WITH v_comments AS ( > SELECT 'this is my comment' AS comment > ) > > Alex You do know about COMMENT ON VIEW v_comments IS

Re: How to keep format of views source code as entered?

Hi Ingolf, For comments in views, I create a unused CTE and put my comments there, e.g. WITH v_comments AS ( SELECT 'this is my comment' AS comment ) Alex Sent with [ProtonMail](https://protonmail.com) Secure Email. ‐‐‐ Original Message ‐‐‐ On Thursday, January 7, 2021 11:19 AM,

Re: How to keep format of views source code as entered?

On Sat, Jan 09, 2021 at 05:26:04PM -0700, "David G. Johnston" wrote: > On Saturday, January 9, 2021, raf wrote: > > > Actually, I just had a look at the pg_views system > > catalog where the source code for views is stored, and > > it doesn't seem to contain enough information to > >

Re: How to keep format of views source code as entered?

On Saturday, January 9, 2021, raf wrote: > > Actually, I just had a look at the pg_views system > catalog where the source code for views is stored, and > it doesn't seem to contain enough information to > reconstruct a create view statement. It only contains > these columns: > > schemaname >

Re: How to keep format of views source code as entered?

On Sat, Jan 09, 2021 at 02:22:25PM +, "Markhof, Ingolf" wrote: > Tom, all, > > when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL > client) marks views that refer to the table using the old column name > as erroneous. So, I can easily identify these cases. And of course

Re: How to keep format of views source code as entered?

Markhof, Ingolf writes: > Tom, all, > > when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL > client) marks views that refer to the table using the old column name as > erroneous. So, I can easily identify these cases. And of course I, as a user, > I am acting in my

Re: How to keep format of views source code as entered?

On Sat, Jan 9, 2021 at 9:22 AM Markhof, Ingolf < ingolf.mark...@de.verizon.com> wrote: > Tom, all, > > when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL > client) marks views that refer to the table using the old column name as > erroneous. So, I can easily identify these

Re: How to keep format of views source code as entered?

> On Jan 9, 2021, at 06:22, Markhof, Ingolf > wrote: > What I would like to have is something that would automatically update the > SQL code in the software repository when I run a CREATE OR REPLACE VIEW. I think you are approaching this backwards. The SQL in the repository should be the

RE: How to keep format of views source code as entered?

Tom, all, when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL client) marks views that refer to the table using the old column name as erroneous. So, I can easily identify these cases. And of course I, as a user, I am acting in my context, i.e. my schema. So it is perfectly

Re: How to keep format of views source code as entered?

Adrian Klaver writes: > On 1/8/21 12:38 AM, Markhof, Ingolf wrote: >> Thanks for your comments and thoughts. >> >> I am really surprised that PostgreSQL is unable to keep the source text >> of a view. Honestly, for me the looks like an implementation gap. >> Consider software development. You

Re: How to keep format of views source code as entered?

On Fri, Jan 08, 2021 at 08:38:29AM +, "Markhof, Ingolf" wrote: > Thanks for your comments and thoughts. > [...] > And you could not even store your source code on the UNIX > system. Instead, you'd be forced to do so in a separate system, like > GitHub. Stupid, isn't it? Right. > [...] >

Re: How to keep format of views source code as entered?

On 1/8/21 12:38 AM, Markhof, Ingolf wrote: Thanks for your comments and thoughts. I am really surprised that PostgreSQL is unable to keep the source text of a view. Honestly, for me the looks like an implementation gap. Consider software development. You are writing code in C++ maybe on a

Re: How to keep format of views source code as entered?

"Markhof, Ingolf" writes: > I am really surprised that PostgreSQL is unable to keep the source text > of a view. Honestly, for me the looks like an implementation gap. Perhaps, but the "gap" is wider than you seem to think. Consider CREATE TABLE t1 (f1 int, f2 text); CREATE VIEW v1 AS SELECT

Re: How to keep format of views source code as entered?

On Fri, 8 Jan 2021, Karsten Hilbert wrote: Am Fri, Jan 08, 2021 at 08:38:29AM + schrieb Markhof, Ingolf: I am really surprised that PostgreSQL is unable to keep the source text of a view. Honestly, for me the looks like an implementation gap. Consider software development. You are writing

Re: How to keep format of views source code as entered?

Am Fri, Jan 08, 2021 at 08:38:29AM + schrieb Markhof, Ingolf: > I am really surprised that PostgreSQL is unable to keep the > source text of a view. Honestly, for me the looks like an > implementation gap. Consider software development. You are > writing code in C++ maybe on a UNIX host. And

RE: How to keep format of views source code as entered?

På fredag 08. januar 2021 kl. 09:38:29, skrev Markhof, Ingolf < ingolf.mark...@de.verizon.com >: Thanks for your comments and thoughts. I am really surprised that PostgreSQL is unable to keep the source text of a view. Honestly, for me the looks like

RE: How to keep format of views source code as entered?

Thanks for your comments and thoughts. I am really surprised that PostgreSQL is unable to keep the source text of a view. Honestly, for me the looks like an implementation gap. Consider software development. You are writing code in C++ maybe on a UNIX host. And whenever you feed you source

Re: How to keep format of views source code as entered?

On Thu, Jan 7, 2021 at 4:38 PM raf wrote: > > Hi, I've only used stored functions (not views or > triggers), Extrapolating to these other types of objects based upon experiences with functions isn't all that helpful. and Postgres has never altered > the code that it stores, Right, you use

Re: How to keep format of views source code as entered?

On Thu, Jan 07, 2021 at 05:33:48PM +, "Markhof, Ingolf" wrote: > So, it looks like PostgreSQL does support saving the original source code of > a view. > > What's best practise to use as a code repository? > > I would expect support of multi-user access, access-right management and >

Re: How to keep format of views source code as entered?

Hello, > On Jan 7, 2021, at 09:33, Markhof, Ingolf > wrote: > > So, it looks like PostgreSQL does support saving the original source code of > a view. To be clear, PostgreSQL itself does not. The suggestion is to use an external source code repository, such as GitHub, GitLab, or one of

RE: How to keep format of views source code as entered?

So, it looks like PostgreSQL does support saving the original source code of a view. What's best practise to use as a code repository? I would expect support of multi-user access, access-right management and perhaps versioning as well…? Thanks for your help! Ingolf From: Markhof, Ingolf

Re: How to keep format of views source code as entered?

"Markhof, Ingolf" writes: > I want the SLQ code of my views stored as I entered it. Is there any way to > achieve this? No. Lots of people prefer to keep their SQL code in some sort of source-code-control system, anyway. regards, tom lane

Re: How to keep format of views source code as entered?

> On Jan 7, 2021, at 08:19, Markhof, Ingolf > wrote: > I want the SLQ code of my views stored as I entered it. Is there any way to > achieve this? Or will I be forced to maintain my views SQL code outside of > PostgreSQL views? The text that you get back from the PostgreSQL system catalogs

How to keep format of views source code as entered?

Hi! Switching from Oracle SLQ to PostgreSQL I am facing the issue that the SQL code the system returns when I open a views source code is different from the code I entered. The code is formatted differently, comments are gone and e.g. all text constants got an explicit cast to ::text added.