Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

On Thu, 2021-12-16 at 11:22 -0800, Bryn Llewellyn wrote: > The advantages are self-evident to these programmers I am not trying to belittle this, but when you are used to system A and start working with system B you always miss some features of A, until you get to know B better and figure out how

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

Hi pá 17. 12. 2021 v 3:39 odesílatel Mladen Gogala napsal: > On 12/16/21 16:48, Pavel Stehule wrote: > > I don't think Postgres needs packages - this is a redundant concept in > > Postgres, when Postgres has schemas (different from Oracle's schemas) > > and extensions. > > > Yes, ORAFCE uses sch

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

On Thu, Dec 16, 2021 at 6:39 PM Mladen Gogala wrote: > I am very well aware of the animosity toward the Oracle community and > the reasons for that animosity. Oracle wiped the floor with the > predecessor of Postgres, the database called "Ingres". Sandra Kurtzig, > the Ingres CEO at the the time,

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

On 12/16/21 16:48, Pavel Stehule wrote: I don't think Postgres needs packages - this is a redundant concept in Postgres, when Postgres has schemas (different from Oracle's schemas) and extensions. Yes, ORAFCE uses schemas as the package names. However, one very practical thing is missing: ses

Re: How to ensure column names are double quoted while using execute format when building a stored procedure?

On 12/16/21 14:11, David G. Johnston wrote: On Thu, Dec 16, 2021 at 3:04 PM Adrian Klaver > wrote: This: 'count of nulls in "UNIQUE_REFERENCE_NUMBER"' is just plain incorrect syntax. count of nulls in quote>UNIQUE_REFRENCE_NUMBER That is not i

Re: How to ensure column names are double quoted while using execute format when building a stored procedure?

On Thu, Dec 16, 2021 at 3:04 PM Adrian Klaver wrote: > > This: > > 'count of nulls in "UNIQUE_REFERENCE_NUMBER"' > > is just plain incorrect syntax. > count of nulls in UNIQUE_REFRENCE_NUMBER That is not invalid syntax. It is just literal single quoted text - the double quotes are simply chara

Re: How to ensure column names are double quoted while using execute format when building a stored procedure?

On 12/16/21 13:11, Shaozhong SHI wrote: When I used SQL identifier, it stopped working.  The command line gets interpreted as following: insert into stats select "1" as id, 'count of nulls in "UNIQUE_REFERENCE_NUMBER"' as checks, count("""UNIQUE_REFERENCE_NUMBER""") from points_of_interest."

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

čt 16. 12. 2021 v 20:22 odesílatel Bryn Llewellyn napsal: > Folks who develop applications for Oracle Database have had the features > that the subject line of this email lists since the arrival of PL/SQL in > the early nineties. The advantages are self-evident to these programmers; > and their l

Re: How to ensure column names are double quoted while using execute format when building a stored procedure?

On Thu, Dec 16, 2021 at 2:33 PM Shaozhong SHI wrote: > I did make it to work and have been experimenting on a number of ways. > But it just does not produce expected results. > > What exactly did you try, what result did it produce, and what did you expect it to produce? David J.

Re: How to ensure column names are double quoted while using execute format when building a stored procedure?

I did make it to work and have been experimenting on a number of ways. But it just does not produce expected results. Regards, David On Thu, 16 Dec 2021 at 21:25, David G. Johnston wrote: > On Thu, Dec 16, 2021 at 2:11 PM Shaozhong SHI > wrote: > >> When I used SQL identifier, it stopped wor

Re: How to ensure column names are double quoted while using execute format when building a stored procedure?

On Thu, Dec 16, 2021 at 2:11 PM Shaozhong SHI wrote: > When I used SQL identifier, it stopped working. The command line gets > interpreted as following: > > insert into stats select "1" as id, 'count of nulls in > "UNIQUE_REFERENCE_NUMBER"' as checks, count("""UNIQUE_REFERENCE_NUMBER""") > from

Re: How to ensure column names are double quoted while using execute format when building a stored procedure?

When I used SQL identifier, it stopped working. The command line gets interpreted as following: insert into stats select "1" as id, 'count of nulls in "UNIQUE_REFERENCE_NUMBER"' as checks, count("""UNIQUE_REFERENCE_NUMBER""") from points_of_interest."pointx_v2_National_Coverage_Sep21" where """UN

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

On Thu, Dec 16, 2021 at 11:22 AM Bryn Llewellyn wrote: > The advantages are self-evident to these programmers; and their lack comes as > a shocking disappointment when they start to write application code for > PostgreSQL*. The absence of packages and inner subprograms is huge. Why are those th

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

On 12/16/21 12:36, Bryn Llewellyn wrote: // /adrian.kla...@aklaver.com wrote: / Not following. To be exposed they have to exist and that is not the case in the community Postgres. The relevant question would seem to be, how do I get these features built?

Re: SELECT DISTINCT scans the table?

Markus Demleitner writes: > Maximally stripped down, my problem is that > select distinct 300 from > seqscans (at least in PostgreSQL 11.14). To me, it seems > obvious that this ought be be just one row containing 300 once > Postgres has established that is nonempty. > Why do things not w

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Folks who develop applications for Oracle Database have had the features >> that the subject line of this email lists since the arrival of PL/SQL in the >> early nineties. The advantages are self-evident to these programmers

Re: How to ensure column names are double quoted while using execute format when building a stored procedure?

On Thu, Dec 16, 2021 at 1:21 PM Shaozhong SHI wrote: > The following command runs but does not produce results as expected. > > Execute Format('insert into stats select %L as id, %2$L as checks, > count(%3$s) from %4$s where %5$s is null', i, 'count of nulls in '||col, > col, t_name, col); > > Al

How to ensure column names are double quoted while using execute format when building a stored procedure?

The following command runs but does not produce results as expected. Execute Format('insert into stats select %L as id, %2$L as checks, count(%3$s) from %4$s where %5$s is null', i, 'count of nulls in '||col, col, t_name, col); There should be a lot of nulls in columns, but it produces o count.

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

On 12/16/21 11:22, Bryn Llewellyn wrote: Folks who develop applications for Oracle Database have had the features that the subject line of this email lists since the arrival of PL/SQL in the early nineties. The advantages are self-evident to these programmers; and their lack comes as a shockin

SELECT DISTINCT scans the table?

Dear list, This feels like a FAQ, but neither the postgres docs nor web searches got me a single step towards some sort of insight. Maximally stripped down, my problem is that select distinct 300 from seqscans (at least in PostgreSQL 11.14). To me, it seems obvious that this ought be be ju

Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

Folks who develop applications for Oracle Database have had the features that the subject line of this email lists since the arrival of PL/SQL in the early nineties. The advantages are self-evident to these programmers; and their lack comes as a shocking disappointment when they start to write a

Re: Why can't I have a "language sql" anonymous block?

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Re your paragraph #2, I already made the case for anonymous procedures. And >> I said that, to deserve the name, they must allow parameterization. They >> bring their value in a certain kind of scripting where you want to d