Practice advice for use of %type in declaring a subprogram's formal arguments

I'm thinking about "language plpgsql" subprograms—but I expect that my question can be generalized to cover "language sql" too. The payload for "create [or replace]" for a "language plpgsql" subprogram specifies various metadata elements like its qualified name, list of formal arguments and the

Re: Practice advice for use of %type in declaring a subprogram's formal arguments

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Do you (all) think that, notwithstanding this, it's a good plan to use >> "%type" for the declaration of a formal argument just for the reason that it >> documents your intention explicitly? > > If my function is likely to

Is the PL/pgSQL refcursor useful in a modern three-tier app?

Section "43.7. Cursors” in the PL/pgSQL chapter of the doc (www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS) starts with this: « Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query r

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Section "43.7. Cursors” in the PL/pgSQL chapter of the doc >> (www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS) >> starts with this: >> « >> Rather than executing a whole query at once, it is

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> laurenz.a...@cybertec.at wrote: > >> b...@yugabyte.com wrote: >> >> Section "43.7. Cursors” in the PL/pgSQL chapter of the doc >> (www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS) >> starts with this: >> >> « >> [...] >> A more interesting usage is to return a

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >>> laurenz.a...@cybertec.at wrote: >>> >>> You seem to think that a client request corresponds to a single database >>> request >> >> …I can’t picture a concrete use case where, not withstanding the "where" >> restriction th

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> adrian.kla...@aklaver.com wrote: > > I have a hard time fathoming why someone who writes documentation does not > actually read documentation. Ouch. In fact, I had read the whole of the "43.7. Cursors" section in the "PL/pgSQL" chapter (www.postgresql.org/docs/15/plpgsql-cursors.html). And th

Re: Schema/user/role

> david.g.johns...@gmail.com wrote: > >> adapt...@comcast.net wrote: >> >> Is there any good reference to explain the best usage of each of these >> structures. I am coming from Oracle. What is the best analog to Oracle's >> "user". > > A schema is a namespace mechanism for objects. It has n

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >>> adrian.kla...@aklaver.com wrote: >>> >>> I have a hard time fathoming why someone who writes documentation does not >>> actually read documentation. >> >> >> Ouch. In fact, I had read the whole of the "43.7. Cursors" sect

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> laurenz.a...@cybertec.at wrote: > > ...I understand that you ask questions to gain deeper understanding. > >> b...@yugabyte.com wrote: >> >> ...I had never come across use cases where [scrollability] was beneficial. I >> wanted, therefore, to hear about some. I thought that insights here woul

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> laurenz.a...@cybertec.at wrote: > >> b...@yugabyte.com wrote: >> >>> laurenz.a...@cybertec.at wrote: >>> >>> I recently used cursor scrollability, so I can show you a use case: >>> >>> github.com/cybertec-postgresql/db_migrator/blob/master/db_migrator--1.0.0.sql#L49 >> >> However, source cod

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com replied to laurenz.a...@cybertec.at: >> >> Thanks for the link to your SQL file at the line where you get the row count >> in the way that you describe... I noted that neither of these functions has >> a refcursor formal argument and tha

My tests show that a WITH HOLD cursor has subtly different semantics from a WITHOUT HOLD cursor

*Summary* My tests show that, when a WITHOUT HOLD cursor has to cache results (see Note 1), then the WHERE clause (if present) is stripped off the cursor's defining SELECT statement and the entire unrestricted result set is cached. But when a WITH HOLD cursor is used, then it’s the *restricted*

Re: My tests show that a WITH HOLD cursor has subtly different semantics from a WITHOUT HOLD cursor

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> *Summary* >> >> My tests show that, when a WITHOUT HOLD cursor has to cache results (see >> Note 1), then the WHERE clause (if present) is stripped off the cursor's >> defining SELECT statement and the entire unrestricted

Why does "fetch last from cur" always cause error 55000 for non-scrollable cursor?

I’m using Version 15.2. I did this test: -- Test One start transaction; declare cur no scroll cursor without hold for select g.val as k, g.val*100 as v from generate_series(1, 10) as g(val) order by g.val; fetch first from cur; fetch next from cur; fetch first from cur; rollback;

Re: Why does "fetch last from cur" always cause error 55000 for non-scrollable cursor?

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com wrote: >> >> But why does "fetch last" fail here: >> >> -- Test Two >> start transaction; >> declare cur no scroll cursor without hold for >> select g.val as k, g.val*100 as v >> from generate_series(1, 10) as g(val

Re: cursors with prepared statements

I found this email from Peter Eisentraut: https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com > I have developed a patch that allows declaring cursors over prepared > statements... This is an SQL standard feature. ECPG already supports it (with > differen

Re: cursors with prepared statements

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> I found this email from Peter Eisentraut: >> https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com >> >> It caused the 42601 error, « syntax error at or near “execute” ». So it >> look

PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements

> b...@yugabyte.com wrote: > >> david.g.johns...@gmail.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> I found this email from Peter Eisentraut: >>> https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com >>> >>> It caused the 42601 error, « syntax error

Re: PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> It seems that there must be different underlying mechanisms at work and that >> this explains why creating a cursor using SQL to execute a prepared >> statement fails but doing this using PL/pgSQL succeeds. What's going on

What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

This tip « It is good practice to create a role that has the CREATEDB and CREATEROLE privileges, but is not a superuser, and then use this role for all routine management of databases and roles. This approach avoids the dangers of operating as a superuser for tasks that do not really require it

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

> jer...@musicsmith.net wrote: > >> b...@yugabyte.com wrote: >> >> This tip >> >> « >> It is good practice to create a role that has the CREATEDB and CREATEROLE >> privileges, but is not a superuser, and then use this role for all routine >> management of databases and roles. This approach avo

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Here's the examples that I mentioned. Please confirm that the changes >> brought by the commit referred to above won't change how it behaves in >> Version 15.2. > > The commit was over only documentation files > > doc/src/

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

laurenz.a...@cybertec.at wrote: > >> b...@yugabyte.com wrote: >> >> I do see that a role that has "createdb" and "createrole" is pretty powerful >> because, for example, a role with these attributes can use "set role" to >> become any other non-superuser (see the example below). > > A user wit

Using "exit" to bring "goto" functionality.

I wanted to see what error(s) would be reported if "exit" or "continue" is used, in PL/pgSQL, outside of a loop—and in particular if these were reported as syntax errors or runtime errors. There were no surprises with "continue". But I was surprised by this: create procedure p() language plpg

Re: Using "exit" to bring "goto" functionality.

> x...@thebuild.com wrote: > >> b...@yugabyte.com wrote: >> >> What is the rationale for supporting what seems to be on its face this >> strange functionality? > > It allows you to EXIT or CONTINUE a loop that is not the innermost one, by > naming the label of an outer loop. One can debate end

Re: Using "exit" to bring "goto" functionality.

> pavel.steh...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> As it happens, Oracle's PL/SQL has a "goto" statement. But PL/pgSQL does >> not. (I assume that this is because "goto" is considered a bad thing.) But >> PL/SQL programmers do use it. However, the doc section: > > The reason

Bryn is retiring. Last day at work Friday 18-Aug-2023

presently using my private email address. Regards, Bryn Llewellyn

i := t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

Given these, where the timestamptz values span the start of US/Pacific DST: t0 timestamptz := ...; t1 timestamptz := ...; i_by_subtraction interval second := t1 - t0; then this expression tests FALSE: t0 + i_by_subtraction i = t1 There's more. We see that while the two "interval second”

Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

Tom Lane wrote: b...@yugabyte.com writes: > There's more. We see that while the two "interval second” values '1 day > 01:00:00' and '25 hours' test as equal, the results of adding each to the > same timestamptz value are different. You're misunderstanding how it works… > PLEASE STATE THE RULES

Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

> On 27-Mar-2021, at 01:42, Francisco Olarte wrote: > > Bryn, ( 1st, sorry if I misquote something, but i use text-only for the list > )... Thanks again, Francisco. You said several things that clarify my understanding. Re your comment: > I've never tried to make some thing as complex as what

Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

> On 27-Mar-2021, at 09:16, Adrian Klaver wrote: > > … Re Adrian’s quote of the doc thus: > Also, field values “to the right” of the least significant field allowed by > the fields specification are silently discarded. Yes, I do take this point. But there’s some murkiness here. All of my test

Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

> adrian.kla...@aklaver.com wrote: > > The point is horology is cultural, see non-Western calendars and alternate > time keeping methods. Trying to maintain a distinction between the two > concepts only furthers the confusion. The inconsistencies you see are the > result of one(culture) interve

Have I found an interval arithmetic bug?

Or am I misunderstanding something? Try this. The result of each “select” is shown as the trailing comment on the same line. I added whitespace by hand to line up the fields. select interval '-1.7 years'; -- -1 years -8 mons select interval '29.4 months';

Re: Have I found an interval arithmetic bug?

br...@momjian.us wrote: > [Thread moved to hackers.] …The best fix I think is… > >> Bryn wrote: Further… there’s a notable asymmetry. The fractional part of >> “1.7 years” is 8.4 months. But the fractional part of the months value >> doesn’t spread further down into days. However, the fractiona

Some abbrev values from pg_timezone_names are not found in pg_timezone_abbrevs

This query: select distinct abbrev as a from pg_timezone_names where abbrev like '%+%' or abbrev like '%-%' order by 1; gets lots of rows with names like these: +00 +01 +12 -07 -08 This query shows that none of these is found in pg_timezone_abbrevs: with v as ( select distinct abbrev as a

The contents of the pg_timezone_names view bring some surprises

Some time zones have abbreviations that are identical to their names. This query: select name from pg_timezone_names where abbrev = name order by name; gets this result: EST GMT HST MST UCT UTC This, in itself, doesn’t seem remarkable. I wondered if any time zones have names that occur as tim

Re: The contents of the pg_timezone_names view bring some surprises

> t...@sss.pgh.pa.us wrote: > >> david.g.johns...@gmail.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> Am I missing an essential clue to resolving what seems to me to be a >>> paradox? Or am I seeing two kinds of bug? >> >> >> You are missing the materi

Re: The contents of the pg_timezone_names view bring some surprises

Tom, David, Adrian, and Peter—thank you all very much for taking an interest in my questions. Your responses have, collectively, been an enormous help. I deleted the text of the exchanges in this particular branch of the overall discussion because it's become rather difficult to work out who sai

Re: The contents of the pg_timezone_names view bring some surprises

> t...@sss.pgh.pa.us wrote: > >> adrian.kla...@aklaver.com wote: >> >>> Bryn wrote: >>> >>> (1) In the context of discussing timezones, the English word "abbreviation" >>> maps to two distinct notions in PG: "pg_timezone_names.abbre

arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why?

The "at time zone" clause that can decorate a timetsamp[tz] value seems to allow an argument that’s an arbitrary expression that yields a value whose data type is "interval". Here’s a contrived exotic example: select '2021-05-21 12:00:00 UTC'::timestamptz at time zone ('2015-05-21 17:00:00'::t

Re: arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why?

Thanks, as ever, Tom, for the quick response. I’ll summarize your explanation as “Yes, there is indeed a logical parsing paradox”. Or, as you said, as “the SQL Standard committee—the gift that keeps on giving”. > Tom wrote: > >> Bryn wrote: >> >> The "at time zone" clause that can decorate a t

inner subprograms ... Was: syntax question

> br...@momjian.us wrote: > > Oh, I thought he wanted to declare a function inside the function that could > be called only by that function, like private functions in Oracle packages > can do. Yes, you can create a function that defines a function that can be > called later. I guess you coul

PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

The problem that I report here seems to be known and seems, too, to astonish and annoy users. It's a bare "computer says No". It's hard to find anything of ultimate use with Google search (either constrained to the PG doc or unconstrained). Here's an example on stackoverflow: https://stackover

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

> t...@sss.pgh.pa.us wrote: > >> pavel.steh...@gmail.com writes: >> >> Some errors like this, but not this can be detected by plpgsql_check >> https://www.google.com/url?q=https://github.com/okbob/plpgsql_check&source=gmail-imap&ust=162914843400&usg=AOvVaw3f9UAP7RvDPC2QKi3_4Mj0 >> - probably

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

> pavel.steh...@gmail.com wrote: > >> t...@sss.pgh.pa.us wrote: >> >>> pavel.steh...@gmail.com wrote: >>> >>> Some errors like this, but not this can be detected by plpgsql_check >>> https://github.com/okbob/plpgsql_check >>> probably the heuristic for type check is not complete. >> >> STRICTMU

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

> pavel.steh...@gmail.com wrote: > > b...@yugabyte.com wrote: > >> pavel.steh...@gmail.com wrote: >> >>> t...@sss.pgh.pa.us wrote: >>> pavel.steh...@gmail.com wrote: >>>

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

> On 10-Aug-2021, at 13:12, Pavel Stehule wrote: > > >> >> r := (select (b, t)::type1 -- it is composite with labels again > > > postgres=# do $$ > declare r record; > begin > r := (select (10,20)); > raise notice '%', to_json(r); > end; > $$; > NOTICE: {"f1":10,"f2":20} > DO > postgres=

Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp()

I'm surprised by the results that I describe below. Please help me interpret them. Briefly, why does transaction_timestamp() report a later value than statement_timestamp() when they're both invoked in the very first statement after "start transaction". (They report identical values in an impli

Thanks, David! Re: Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp()

> David G. Johnston wrote: > > Bryn wrote: > > I'm surprised by the results that I describe below. Please help me interpret > them. > > Briefly, why does transaction_timestamp() report a later value than > statement_timestamp() when they're both invoked in the very first statement > after "st

Re: Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp()

> Adrian Klaver wrote: > > On 9/21/21 4:23 PM, Bryn Llewellyn wrote: >> I'm surprised by the results that I describe below. Please help me interpret >> them. >> Briefly, why does transaction_timestamp() report a later value than >> statement_timestamp()

Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp()

I'm surprised by the results that I describe below. Please help me interpret them. Briefly, why does transaction_timestamp() report a later value than statement_timestamp() when they're both invoked in the very first statement after "start transaction". (They report identical values in an impli

Using make_timestamp() to create a BC value

Everything that I say here applies to "make_timestamptz()", too. But my code examples need less typing when I use plain "make_timestamp()". It would seem to me that ordinary principles of good software design let one expect that the rule that this procedure tests ought to hold: create procedure

Re: Using make_timestamp() to create a BC value

> t...@sss.pgh.pa.us wrote: > >> Bryn wrote: >> >> But this invocation makes the assertion fail: >> call assert_make_ts_from_extracted_fields_from_ts_ok('0001-01-01 00:00:00 >> BC'); >> The 22008 error is reported thus: >> date field value out of range: -1-01-01 > > As of v14, make_timestamp[tz

"two time periods with only an endpoint in common do not overlap" ???

I’m quoting this from “9.9. Date/Time Functions and Operators” in the PG Version 14 doc on the “overlaps” operator, here: www.postgresql.org/docs/14/functions-datetime.html It’s the same in “current”—and in the Version 11 doc. « This expression yields true when two time periods (defined by thei

Re: "two time periods with only an endpoint in common do not overlap" ???

> David Johnston wrote: > >> Bryn wrote: >> >> This means for instance that two time periods with only an endpoint in >> common do not overlap. > > A range has two endpoints. The one at the later (end) of the range and the > one at the earlier (start). I suppose rewording it to say “boundary

Where is the tsrange() function documented?

I found my way to this by guesswork and good luck. (I happen to be using PG Version 13.5. But I don't suppose that this matters.) Doing "\df tsrange()" gives this: Schema | Name | Result data type | Argument data types | Type +

Re: Where is the tsrange() function documented?

> David Johnston wrote: > >> Bryn wrote: >> >> Where is it? > > TOC - Data Types - Range Types: > > https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN > Thanks for the instant response, David.

Re: Where is the tsrange() function documented?

> Adrian wrote: > >> Bryn wrote: >> >> I found my way to this by guesswork and good luck. (I happen to be using PG >> Version 13.5. But I don't suppose that this matters.)… But I can't formulate >> a search that finds it using the doc site's intrinsic search. > > Where exactly did you search?

Re: Where is the tsrange() function documented?

> David Johnston wrote: > >> Bryn wrote: >> >> I can't find anything, neither on the page in question here on Range Types >> nor in the doc on the overlaps operator, about the semantics for when a >> duration collapses to an instant. Am I missing this too? > > Same page: > > https://www.postg

Looking for a doc section that presents the overload selection rules

I fear that I'm about to embarrass myself again. So I'll just ask for forgiveness in advance. Here's a simple test to get started. (All tests are done in a session where I set the timezone to 'UTC'.) drop function if exists f(text)cascade; drop function if exists f(timestamp) cascade;

Re: Looking for a doc section that presents the overload selection rules

> Adrian Klaver wrote: > >> Bryn wrote: >> >> I fear that I'm about to embarrass myself again. So I'll just ask for >> forgiveness in advance. Here's a simple test to get started. (All tests are >> done in a session where I set the timezone to 'UTC'.) >> >> drop function if exists f(text)

Re: Looking for a doc section that presents the overload selection rules

> On 21-Oct-2021, at 17:15, Adrian Klaver wrote: > > On 10/21/21 15:45, Bryn Llewellyn wrote: >>> /Adrian Klaver wrote:/ >>> >>>> /Bryn wrote:/ >>>> > >> Thanks, too, to David Johnston for your reply. Yes, I see now that the >&g

Re: Looking for a doc section that presents the overload selection rules

> adrian.kla...@aklaver.com wrote: > > Bryn wrote: > >>> adrian.kla...@aklaver.com wrote: >>> >>> Bryn wrote: > Adrian Klaver wrote: > ... >> You've lost me entirely here, I'm afraid. >> My question was simple: why is there no "to_char ( date, text ) → text" >> overload? > > Because th

Re: Looking for a doc section that presents the overload selection rules

> david.g.johns...@gmail.com wrote: > >> Bryn wrote: >> >> There could, so easily, have been three “to_char()” overloads for these >> three data types… > > The argument about avoiding the implicit cast, and thus being easier for > newcomers to figure out, is the compelling one for me. But, fra

Re: Looking for a doc section that presents the overload selection rules

> t...@sss.pgh.pa.us wrote: > > Bryn Llewellyn writes: >> There could, so easily, have been three “to_char()” overloads for these >> three data types that honored the spirit of the “::text” typecast by >> rendering only what’s meaningful, despite what the template

to_date() and to_timestamp() with negative years

I'm quoting here from "Usage notes for date/time formatting" just below "Table 9.25. Template Pattern Modifiers for Date/Time Formatting" here: https://www.postgresql.org/docs/current/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE on the page "9.8. Data Type Formatting Function

Re: to_date() and to_timestamp() with negative years

> t...@sss.pgh.pa.us wrote: > > Bryn Llewellyn writes: >> Is there any chance that you might be bold and simply make negative "year" >> values illegal in "to_date()" and "to_timestamp()" — just as they already >> are in &qu

Re: to_date() and to_timestamp() with negative years

adrian.kla...@aklaver.com wrote: > Bryn wrote: > >>> t...@sss.pgh.pa.us wrote: >>> >>> Bryn Llewellyn writes: >>>> Is there any chance that you might be bold and simply make negative "year" >>>> values illegal in &

Re: to_date() and to_timestamp() with negative years

> t...@sss.pgh.pa.us wrote: > > Adrian Klaver writes: >> Haven't had time to work through what the above is actually doing. > > I think the first two are explained by 489c9c340: > >Also, arrange for the combination of a negative year and an >explicit "BC" marker to cancel out and produc

Re: to_date() and to_timestamp() with negative years

> adrian.kla...@aklaver.com wrote: > > On 11/3/21 17:00, Adrian Klaver wrote: >> On 11/3/21 15:56, Bryn Llewellyn wrote: >>>> t...@sss.pgh.pa.us wrote: >>>> > >>> And you have to have some kind of separator between the years substring and >

Re: to_date() and to_timestamp() with negative years

> adrian.kla...@aklaver.com wrote: > > Not sure how this can be handled in a totally predictable way given the > unpredictable ways in which datetime strings are formatted? > > The only thing I can say it is it points out that when working with datetimes > settling on a standard format is your

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

My question is this: Was there a deliberate decision not to allow a “language sql” anonymous block? Or is it just that nobody thought that it would be useful? Here’s what I mean. First, something that works (using PG Version 14.1): create procedure p_plpgsql() security definer language plpg

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

ecute-many using an anonymous block. (This is another counter intuitive restriction that, today, has to be learned.) t...@sss.pgh.pa.us wrote: Bryn Llewellyn writes: > Was there a deliberate decision not to allow a “language sql” anonymous > block? Or is it just that nobody thought that

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

Adrian Klaver wrote: Bryn wrote: > Well… that’s the answer: “nobody thought it’d be useful”. Thanks, Tom. > The difference between using a “language sql” anonymous block and just > executing the contained SQL statements? is partly a clear declaration of the > intent of your code and a guarantee

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

> david.g.johns...@gmail.com wrote: > >> On Monday, December 13, 2021, Bryn Llewellyn > <mailto:b...@yugabyte.com>> wrote: >> >> There must be a reason to prefer a “language sql” procedure over a “language >> plpgsql” procedure—otherwise the former wo

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

> mmonc...@gmail.com wrote: > >> Bryn wrote: >> >>> david.g.johns...@gmail.com wrote: >>> Bryn wrote: There must be a reason to prefer a “language sql” procedure over a “language plpgsql” procedure—otherwise the former wouldn’t be supported. >>> >>> I would say that is tru

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

> david.g.johns...@gmail.com wrote: > >> adrian.kla...@aklaver.com wrote: >> >>> Bryn wrote: >>> >>> I used a procedure to test this because functions shouldn’t do DDL. I >>> started with a working “language plpgsql” example… >> >> Since procedures are relatively new to Postgres you are going

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

> adrian.kla...@aklaver.com wrote: > >> Bryn wrote: >> >>> mmonc...@gmail.com wrote: >>> >>> SQL language functions have one clear advantage in that they can be inlined >>> in narrow contexts; this can give dramatic performance advantages when it >>> occurs. They have a lot of disadvantages: >

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

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: 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: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

laurenz.a...@cybertec.at wrote: > >> b...@yugabyte.com 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 bett

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

> p...@bowt.ie wrote: > >> b...@yugabyte.com wrote: >> >> Modular design recommends exposing functionality through a purpose oriented >> interface and hiding all implementation details from the API’s user. A >> package achieves this with declarative syntax via the spec/body separation. >> The

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

b...@yugabyte.com wrote: > >> p...@bowt.ie wrote: >> >>> b...@yugabyte.com wrote: >>> >>> Modular design recommends exposing functionality through a purpose oriented >>> interface and hiding all implementation details from the API’s user. A >>>

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

> pavel.steh...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> I’m still hoping that I might get some pointers to whitepapers or blog posts >> that expand on those bullets that I quoted from the PG doc: «Instead of >> packages, use schemas to organize your functions into groups.» and «Si

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

> pavel.steh...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >>> pavel.steh...@gmail.com wrote: >>> b...@yugabyte.com wrote: I’m still hoping that I might get some pointers to whitepapers or blog posts that expand on those bullets that I quoted from the PG doc: «Inste

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

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Is this model not used for the PostgreSQL system? > > It is not. Basically we have an "Proposed Patches" tracker and they either > get committed, or they do not. Once committed, new features become available > at the nex

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

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Is this model not used for the PostgreSQL system? > > It is not. Basically we have an "Proposed Patches" tracker and they either > get committed, or they do no

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

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> update t set t.v = p.v where t.k = p.k; >> >> At run-time, p() terminates with an obscurely worded error: >> >> 42703: column "t" of relation "t" does not exist. > > "set t.v" is simply invalid SQL and the error has noth

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

> pavel.steh...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Yes, I did read your “schema variables” post on your site “Pavel Stehule’s >> blog — Some notes about PostgreSQL”. It gives me a very good idea about what >> you have in mind. >> >> But as I’ve come to understand the term “Fu

Re: psql does not provide proper response

> shishaozh...@gmail.com wrote: > > I do not know what happened. > > psql does not provide proper response anymore. > > I typed the following and see nothing. > > user=# select * from boundaryline.scotland_and_wales_const_region > user-# > > Can anyone enlighten me? This happens to me all too

Re: DELETING then INSERTING record with same PK in the same TRANSACTION

> david.g.johns...@gmail.com wrote: > > The convention on these lists is to inline or bottom post (and to trim the > reply to just the pertinent parts). Just for completeness, I expected this test to run without error. (I tried it in PG Version 14.1). create table t(k int primary key, v text n

"grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

*Summary* If user "x" owns function "s.f()", and if you want user "z" to be able to execute it, then this alone is insufficient: grant execute on function s.f() to z; The attempt by "z" to execute "s.f()" this draws the 42501 error, "permission denied for schema s". But this _is_ sufficient:

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> grant usage on schema s to z; >> revoke execute on function s.f() from z; -- Yes, really! >> >> *This surprises me* >> >> The PG doc on, in the "5.7. Privileges" section at >> https://www.postgresql.org/docs/current/ddl-pr

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

> david.g.johns...@gmail.com wrote: > >> t...@sss.pgh.pa.us wrote: >> >> (I wonder if it'd be practical or useful to emit a warning when granting >> permissions on an object that already has a grant of >> the same permissions to PUBLIC. That would at least cue people who don't >> understand ab

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

>> b...@yugabyte.com wrote: >> >> Which catalog relations are sufficient to support a query that lists out, >> for example, every user-defined function and procedure with its (at least >> first-level) grantees? > > adrian.kla...@aklaver.com wrote: > > Tip if you do: > > psql -d test -U postgr

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

> rjuju...@gmail.com wrote: > >> adrian.kla...@aklaver.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> I s'pose that I can interpret this output in the light of the "miriam" >>> example by guessing than an empty LHS means "public" and that the initial >>> "X" means "execute". It looks like

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

> ddevie...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> …Then I did this: >> >> with c as ( >> select >>proname::text as name, >>pronamespace::regnamespace::text as schema, >>aclexplode(proacl)as "aclexplode(proacl)" >> from pg_catalog.

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

> b...@yugabyte.com wrote: > >> ddevie...@gmail.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> …Then I did this: >>> >>> with c as ( >>> select >>>proname::text as name, >>>pronamespace::regnamespace::text as schema, >>>aclexplode(proacl)as

<    1   2   3   4   >