Hi
pá 5. 11. 2021 v 4:47 odesílatel Michel Pelletier <
pelletier.mic...@gmail.com> napsal:
> The docs for expanded data types are good, but for a working example you
> have to go trolling through the array data type source code, which is
> enlightening but a pretty heavy lift for me especially if
Hi
út 16. 11. 2021 v 18:23 odesílatel DAVID ROTH napsal:
> One of the nice things about Oracle packages is that the code is loaded
> and global values are set and stored only once per session. This is very
> useful for values that are used repeatedly.
>
> What is the best way of emulating this b
ackages. Have I
> missed something?
>
There is nothing similar in Postgres.
I am working session variables, that should be created in schema too. As
workaround you can use GUC - configuration variables, that can be used for
storing private values too.
Regards
Pavel
> On 11/16/2021 12:27
alues_ct() line 15 at EXECUTE
>
> Please I would be very grateful for any hints as to what I could be doing
> wrong.
>
This is not MS SQL - result of last query is not result of function.
When you want to see result, you should to use RETURN statement - in this
case RETURN QUERY EXECUTE, and your function should to return SETOF text
instead VOID.
Regards
Pavel Stehule
>
> Regards
>
>
>
Hi
st 1. 12. 2021 v 6:28 odesílatel Garfield Lewis
napsal:
> Thx, Tom...
>
> But isn't the libxml2 library not sufficient for this purpose? Note that I
> have not tried it yet, I am still just investigating possible solutions.
>
Unfortunately, no, or it is not about Postgres. The development of
po 6. 12. 2021 v 18:21 odesílatel Francisco Olarte
napsal:
> On Mon, 6 Dec 2021 at 18:03, Alan Hodgson
> wrote:
> ...
> > The table has nearly 29 million records. 5069 of them match
> shipment_import_id = 5090609. There is an index on shipment_import_id,
> which the planner happily uses without
č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
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 ext
Hi
po 20. 12. 2021 v 13:31 odesílatel iulian dragos <
iulian.dra...@databricks.com> napsal:
> Hi,
>
> I was analyzing the query performance in a certain code path and noticed
> that practically all of the query time is spent planning (11s planning,
> 200ms execution time). Here is the output of E
> too much in this direction?
>
https://wiki.postgresql.org/wiki/Show_database_bloat
Pavel
>
> iulian
>
>
> On Mon, Dec 20, 2021 at 1:42 PM Pavel Stehule
> wrote:
>
>> Hi
>>
>> po 20. 12. 2021 v 13:31 odesílatel iulian dragos <
>> iulian.dra...@databr
Hi
> 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
> «Since there are no packages, there are no package-level variables eit
út 21. 12. 2021 v 19:28 odesílatel Bryn Llewellyn
napsal:
> *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, us
út 21. 12. 2021 v 19:58 odesílatel Michael Lewis
napsal:
> On Tue, Dec 21, 2021 at 11:50 AM Pavel Stehule
> wrote:
>
>> I wrote about it. Did you read this article?
>>
>> https://okbob.blogspot.com/2018/02/schema-variables.html
>>
>> The goals of this pro
Hi
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 “Functional Spec”, this denotes a
> formal deliverable that a product develop
>
> From PostgreSQL's SQL perspective the session variables are common
> database objects (contra SQL/PL where package variables are SQL/PL language
> objects), and SQL disallows ambiguity. This is a little bit more complex
> problem, because session variables can be used everywhere in Postgres (no
>
> Thank you very much indeed for this careful reply, Pavel—and for the links
> to the threads on the Hackers list. A great deal is now clarified for me.
> You said “I am not native speaker, and my English is very poor”. You’re
> far, far, too modest. I am a native English speaker. And I often see
Hi
po 27. 12. 2021 v 9:55 odesílatel Alexey Murz Korepov
napsal:
> MySQL in version have deprecated the `MYSQL_PWD` environment variable,
> because they considers this way as insecure, quote from
> https://dev.mysql.com/doc/refman/8.0/en/environment-variables.html#idm45429554761920
> :
>
> > Us
Hi
út 28. 12. 2021 v 13:29 odesílatel Narendra katlamudi <
narendra_katlam...@yahoo.co.in> napsal:
> Hi Team
>
> When I use psql to connect a pg server, I am getting below error. Any
> solution/pointers?
> I have pg versions 11,12,13 installed on my system.
>
> /usr/lib/postgresql/13/bin/psql: sy
Hi
čt 6. 1. 2022 v 14:33 odesílatel Andreas Joseph Krogh
napsal:
> På torsdag 06. januar 2022 kl. 14:29:12, skrev David G. Johnston <
> david.g.johns...@gmail.com>:
>
> [..]
> The fact is that the ‘ - - > ‘ operator gives you the needed output.
>
> David J.
>
>
> Yeah, I think that's the correct
út 11. 1. 2022 v 10:54 odesílatel napsal:
> Hi,
>
> I would like to submit a problem (bug ?) that I encountered while handling
> temporary tables in plpgsql functions.
>
> First, if I create a TABLE and a TEMP TABLE with the same name, and I
> request without specified the schema, the temporary t
út 11. 1. 2022 v 16:51 odesílatel napsal:
> Hi, Thank you for pointing this part of the documentation.
> It's actually works with EXECUTE 'INSERT INTO my_table VALUES((random() *
> 100)::INT);'; INSTEAD OF INSERT INTO my_table VALUES((random() * 100)::INT);
> And it's possible to suppose that th
čt 20. 1. 2022 v 19:50 odesílatel Bryn Llewellyn napsal:
> > 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_
Hi
pá 4. 2. 2022 v 7:02 odesílatel rob stan napsal:
> Hello,
>
> We have "pgq" extensions on our clusters when I am trying to upgrade from
> 9.6.24 to 14.1 with pg_upgrade --link method, i am getting error;
>
>
> First i was getting this error ;
>
> could not load library "$libdir/pgq_lowlevel":
ne 13. 2. 2022 v 9:29 odesílatel Peter J. Holzer napsal:
> On 2022-02-13 01:11:16 +0100, Andreas 'ads' Scherbaum wrote:
> > On 12/02/2022 22:34, Peter J. Holzer wrote:
> > > On 2022-02-12 22:09:25 +0100, Andreas 'ads' Scherbaum wrote:
> > > > On 12/02/2022 20:50, Peter J. Holzer wrote:
> > > > >
ne 13. 2. 2022 v 10:45 odesílatel Guyren Howe napsal:
>
> The MySQL autocomplete is designed without context filtering. Maybe we can
> have this implementation too (as alternative)
>
> so using all column names + all table names + aliases.column names (when
> we know defined alias)
>
> Another id
Hi
pá 18. 2. 2022 v 14:24 odesílatel Mladen Gogala
napsal:
> On 2/17/22 13:10, Mladen Gogala wrote:
>
> Hi!
>
> I am getting the following error when trying to PREPARE transaction which
> updates both local and foreign table:
>
> 2/17/22
> 12:48:00:657 EST] 0128 RegisteredRes E WTRN0046E:
Hi
2017-12-13 16:22 GMT+01:00 Olga Lytvynova-Bogdanova <
olytvynovabogdan...@gmail.com>:
> Hello,
> I would like to ask whether is VARBIT a TOAST-able type and table should
> support row insertion with VARBIT values more than 8 KB in size thus?
> Regards
> Olha
>
postgres=# select * from pg_type
2017-12-18 17:13 GMT+01:00 Nick Dro :
>
> Hi,
> I know how to implement this. It's not the issue.
> It's very easy to implement absolute value as well yet still PostgreSQL
> gives abs(x) function which is build in function.
> My claim is that if there is a build in function for absolute value why
2017-12-19 10:13 GMT+01:00 Nick Dro :
> This is exactly why I think there should be some build-in function for
> that...
> Percentage calculation exists in almost any databse and information system
> - it requires from use to implement many functions on thier own for
> something that is very basic
Hi
2017-12-19 10:53 GMT+01:00 gregoryrevilla :
> Sorry may be it is not an appropriate question directly for this topic but
> how to make my app configured to use UTF8?
> Thank you
>
what doesn't work? Can you send more informations?
Regards
Pavel
>
>
>
> -
> Help for app promoters htt
Hi
2017-12-23 19:53 GMT+01:00 Timo Myyrä :
> Hi,
>
> I'm preparing migration of our asset management system database from
> Oracle 12c to
> PostgreSQL 10. I'm using ora2pg and a bit of sed to mangle the SQL ready
> for
> import to pg but I've hit first problem:
> ERROR: referenced relation "..."
2017-12-26 14:44 GMT+01:00 Martin Marques :
> El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió:
> > Recently I had a problem with a base file with size 0 in a standby
> server.
> >
> > This raised one question: does PostgreSQL (9.6.6) check base integrity
> > at startup?
> >
> > At
2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter <
rich...@simkorp.com.br>:
> Em 26/12/2017 12:25, Pavel Stehule escreveu:
>
>
>
> 2017-12-26 14:44 GMT+01:00 Martin Marques
> :
>
>> El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió:
>
2017-12-26 16:50 GMT+01:00 Edson Carlos Ericksson Richter <
rich...@simkorp.com.br>:
> Em 26/12/2017 13:40, Pavel Stehule escreveu:
>
>
>
> 2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter <
> rich...@simkorp.com.br>:
>
>> Em 26/12/2017 12:25, Pave
Hi
2018-01-16 17:44 GMT+01:00 hmidi slim :
> Sorry I forget the lower command when I wrote the code, it is like this:
> lower(g.country_code) like lower('US')
> (lower(g.feature_class) like lowwer('P') or lower(g.feature_class) like
> lower('L'))
>
please, don't do top post.
Your query must be
ndexes?
>
https://en.wikipedia.org/wiki/Posting_style#Top-posting .. please, don't do
it.
IS DISTINCT FROM has sense if your data - or your queries has NULL. If not,
and it is probably your case, then <> should be preferred.
Regards
Pavel
> 2018-01-16 17:49 GMT+01:00 Pavel Steh
2018-01-16 19:35 GMT+01:00 hmidi slim :
> Thank you for your advices and thanks for all people who give me some best
> practises and useful ideas.
>
you are welcome
Regards
Pavel
2018-02-11 14:50 GMT+01:00 PegoraroF10 :
> but DO doesn´t return values, or it does ?
>
> execute block returns(ID Integer, Name varchar(50), LastInvoice Date, ...)
> as
> begin
> for select ID, Name from Customers where ... into ID, Name do begin
> select bla, bla, bla from functionX(ID) in
2018-02-16 13:31 GMT+01:00 mariusz :
>
> hello all,
>
> i just noticed some strange thing in plpgsql, that is keyword RETURN is
> allowed as noop after a valid statement.
> shame on me, after so many years of using plpgsql i happened to write a
> bug omitting semicolon after statement just before
Hi
2018-02-16 13:20 GMT+01:00 Thiemo Kellner :
> Hi all
>
> I would like to have a generic trigger function that compares on insert if
> there is already a record in the table with the very same values. Using
> PL/pgSQL ( I am not bound to that) I know the insert record structure from
> the new r
2018-02-16 14:20 GMT+01:00 mariusz :
> On Fri, 2018-02-16 at 13:51 +0100, Pavel Stehule wrote:
>
>
> > It is not a bug, it is feature. Sometimes not nice. RETURN is keyword
> > in procedural part, but it is nothing in sql part.
> >
> thanks, i haven't thought
2018-02-18 14:41 GMT+01:00 Vitaliy Garnashevich :
>
> I certainly wouldn't recommend using 1/2 of RAM right away. There's a
>> good chance it would be a waste of memory - for example due to double
>> buffering, which effectively reduces "total" cache hit ratio.
>>
>
> Double buffering is often men
Hi
2018-02-22 5:59 GMT+01:00 Marcin Giedz :
> Hi, there are at least 5 tools I found on the PG list but could you
> recommend well tested, free one ? we need to migrate production 30GB oracle
> 11 db to postgres 9 and are looking for best approach. Of course if there
> is no free/open solution an
Hi
use format function
do $$
begin
for i in 1..10
loop
execute format($_$
create or replace function %I(a int)
returns int as $__$
begin
return a + %s;
end;
$__$ language plpgsql;
$_$, 'foo_' || i, i);
end loop;
end;
$$;
DO
(2024-09-30 12:21:29) postgres=# \sf foo_1
CREATE OR REPLACE
pá 27. 12. 2024 v 22:03 odesílatel Tom Lane napsal:
> "David G. Johnston" writes:
> > It is what it is - and if one is not careful one can end up writing
> > hard-to-understand and possibly buggy code due to the various execution
> > environments and caches involved.
>
> Yeah, I don't see this c
Hi
pá 27. 12. 2024 v 21:26 odesílatel David G. Johnston <
david.g.johns...@gmail.com> napsal:
> On Friday, December 27, 2024, Jan Behrens wrote:
>>
>>
>> It seems that it matters *both* how the search_path was set during the
>> *first* invocation of the function within a session *and* how it is
Hi
> Maybe not many people run into these issues because schemas and
> functions aren't used as often in combination?
>
I think schema and functions are common combinations. But when people have
objects with the same name, then they are careful to be sure, so objects
have really identical struct
út 4. 2. 2025 v 5:09 odesílatel Marcelo Fernandes
napsal:
> On Mon, Feb 3, 2025 at 6:46 PM Pavel Stehule
> wrote:
> > The queries executed by SPI are never executed on the top level. These
> queries are marked as nested.
> >
> > So you need to use auto_explain
> h
Hi
po 3. 2. 2025 v 0:06 odesílatel Marcelo Fernandes
napsal:
> Hi there,
>
> I have been trying to debug what queries an extension is firing. After
> reading
> the code for the extension, I noticed that all the statements are fired
> via the
> SPI interface, most specifically, using the SPI_exec
Hi
ne 15. 12. 2024 v 17:59 odesílatel Ron Johnson
napsal:
> https://www.postgresql.org/docs/current/catalog-pg-namespace.html
>
> Currently, when I want to query all "userland" tables, I write something
> like:
> select ...
> from pg_class cl, pg_namespace nsp
> where cl.relnamespace = nsp.oid
>
Hi
so 23. 11. 2024 v 16:01 odesílatel napsal:
> I get get this same error
>
> syntax error at or near "$1" at character 15
>
> if I feed "const char *command" with the following texts.
>
> SET TIME ZONE $1
> SET TIME ZONE $1::TEXT
>
> For some reasons, I can not add quotes around $1 as follows.
Hi
čt 2. 1. 2025 v 11:37 odesílatel Jan Behrens
napsal:
> On Wed, 1 Jan 2025 11:19:32 -0700
> "David G. Johnston" wrote:
>
> > On Wed, Jan 1, 2025 at 10:55 AM Jan Behrens
> wrote:
> >
> > > On Sat, 28 Dec 2024 00:40:09 +0100
> > > Jan Behrens wrote:
> > >
> > > > On Fri, 27 Dec 2024 13:26:28
čt 2. 1. 2025 v 13:15 odesílatel Jan Behrens
napsal:
> On Thu, 2 Jan 2025 12:40:59 +0100
> Pavel Stehule wrote:
>
> > How can you identify unwanted usage of non qualified identifiers from
> > wanted usage of non qualified identifiers? It is a common pattern for
> > sh
Hi
> >
> > some times can be pretty ineffective to have database per customer - more
> > connect, disconnect in postgres is much more expensive than SET
> search_path
> > TO .. and maybe RESET plans;
>
> I guess that means there is a practical application where search_path
> MAY change at runtime
st 19. 3. 2025 v 18:14 odesílatel Álvaro Herrera
napsal:
> Hello
>
> On 2025-Mar-19, Siraj G wrote:
>
> > I have a PG (v16) instance which is occupying around 1TB of storage. Out
> of
> > this, around 350GB is occupied by the table pg_catalog.pg_attribute.
> > Why is the catalog table's size so b
Hi
út 4. 3. 2025 v 18:30 odesílatel Igor Korot napsal:
> Hi, ALL,
> Trying to execute following query:
>
> [code]
> queries.push_back( L"DO $$ BEGIN IF NOT EXISTS( SELECT 1 FROM
> pg_class c, pg_namespace n WHERE n.oid = c.relnamespace AND c.relname
> = \'abcatc_x\' AND n.nspname = \'pub
Hi
ne 23. 3. 2025 v 19:31 odesílatel Igor Korot napsal:
> Hi,
>
> [code]
> SELECT current_setting('server_version_num')::int > 13 as v13
> \gset
> \if :v13
>CREATE OR REPLACE TRIGGER playersinleague_insert AFTER INSERT ON
> playersinleague WHEN new.current_rank IS NULL
>BEGIN
>
401 - 457 of 457 matches
Mail list logo
