Multiple Indexes

I understand the planner can use multiple indexes to get the best plan. Can someone point me a paper that explains how this works. Thanks

Oracle to Postgress Migration

Has anything been published on Oracle to Postgress migration. I am finding plenty of information about schema migration but, I think this is the easy part. I have a a tremendous amount of SQL and PL/SQL code that needs to be translated. I know Oracle "packages" will not translate. DECODE is not

Re: Oracle to Postgress Migration

Looks good. Thanks > On 07/14/2022 3:10 PM Bruce Momjian wrote: > > > On Thu, Jul 14, 2022 at 03:06:58PM -0400, DAVID ROTH wrote: > > Has anything been published on Oracle to Postgress migration. > > > > I am finding plenty of information about schema migratio

Re: Stored procedure code no longer stored in v14 and v15, changed behaviour

Is there a way to reverse engineer the original code (or its equivalent) from what is saved in the database? > On 12/02/2022 8:48 AM Dominique Devienne wrote: > > > On Thu, Dec 1, 2022 at 8:51 PM Tom Lane wrote: > > Do you really fail to see the contradictions in this? You want the > > data

Schemas and Search Path

Is there any practical limit on the number of schemas in a database? Will the number of schemas in a user's search path impact performance? Thanks Dave Roth > On 03/20/2023 10:15 AM Ron Johnson wrote: > > > Real-time CDC is the difficult part. ora2pg (using views) can do a > static m

Schema/user/role

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". Thanks Dave Roth > On 03/20/2023 10:15 AM Ron Johnson wrote: > > > Real-time CDC is the difficult part. ora2pg (using views) can do

Trigger questions

1) Can I create a trigger on a view? 2) Do triggers cascade? Say I have an insert trigger on a table. And, I have an insert trigger on a view that references this table If I do an insert on the view, will both triggers fire?

Trigger Function question

In a trigger function, is there a way to get a list of all of the columns in the triggering table? I want to be able to use a single trigger function with multiple tables. Thanks

Re: Trigger Function question

phe Pettus wrote: > > > > On Jul 10, 2023, at 11:20, DAVID ROTH wrote: > > > > In a trigger function, is there a way to get a list of all of the columns > > in the triggering table? > > You can get the table that the trigger fired on with TG_TABLE_SCHEMA a

Re: Trigger Function question

Thanks for the example. I have a test trigger now that does that but my application needs all of the columns. > On 07/10/2023 2:31 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:29, DAVID ROTH wrote: > > > > I want to use a single trigger function

Re: Trigger Function question

I was hoping that NEW could be treated as a record or as an arrayy similar to pg_argv. > On 07/10/2023 2:31 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:29, DAVID ROTH wrote: > > > > I want to use a single trigger function to log multiple tabl

Re: Trigger Function question

I saw your message about a "few" columns and missed the new.* notation. Is there a way to get new.* into a jsonb column? > On 07/10/2023 2:38 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:37, DAVID ROTH wrote: > > > > Thanks for the ex

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

I am a newbe at my current job. They have a separate log table for every table because they want all the columns. I have been looking for a way to go to a common log table without requiring major changes to the application. With your help, I have a proof of principle that I can demonstrate now.

Re: Is data import from Oracle table to Postgres table in Ora2pg consecutive or random?

I have not tried this in a while but I think a SELECT with a "hint" will return rows in the order of the index in the hint. This does NOT work for distributed queries. > On 09/12/2023 9:10 AM EDT Daniel Gustafsson wrote: > > > > On 12 Sep 2023, at 14:26, Matthias Apitz wrote: > > > > El

Re: The tragedy of SQL

There was also QUEL. The original language for Ingress out of UCB. > On 09/14/2021 9:51 AM David Goodenough > wrote: > > > On Tuesday, 14 September 2021 14:06:13 BST Merlin Moncure wrote: > > On Tue, Sep 14, 2021 at 12:32 AM Guyren Howe wrote: > > > If I had $5 mill

Postgres Equivalent of Oracle Package

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 behavior in Postgresql?

Re: Postgres Equivalent of Oracle Package

vel Stehule wrote: > > > Hi > > út 16. 11. 2021 v 18:23 odesílatel DAVID ROTH mailto:adapt...@comcast.net > 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

General Performance Question

I am working on a large Oracle to Postgres migration. The existing code frequently constructs a string and then uses Oracle's "EXECUTE IMMEDIATE" to run it. "EXECUTE" has the same functionality in Postgres. For example: CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number) RETURN VARCHAR2 A

Re: General Performance Question

ting code that does not have any dynamic elements. I am also hoping to get a better understanding of the way the Postgres optimizer works. Any links you could suggest for this would be appreciated. > On 11/18/2021 9:27 AM Thomas Kellerer wrote: > > > DAVID ROTH schrieb am 18.11