Hi,
I am writing a trigger function that is used after DELETE, INSERT, and
UPDATE, like so:
CREATE TRIGGER tr_name AFTER DELETE OR INSERT OR UPDATE ...
How can I tell inside the trigger function if the event was DELETE or
INSERT/UPDATE?
The table has a `NOT NULL id` column, so I am thi
On 7/11/2018 10:38 AM, Adrian Klaver wrote:
On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote:
How can I tell inside the trigger function if the event was DELETE or
INSERT/UPDATE?
https://www.postgresql.org/docs/10/static/plpgsql-trigger.html
This looks like it have all of the information that
On 7/11/2018 11:02 AM, David G. Johnston wrote:
On Wed, Jul 11, 2018 at 10:54 AM, Igal @ Lucee.org <mailto:i...@lucee.org>>wrote:
On 7/11/2018 10:38 AM, Adrian Klaver wrote:
On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote:
How can I tell inside the trigger fu
I have a custom search_path:
# show search_path;
search_path
--
"staging, transient, pg_catalog"
(1 row)
I ran `pg_dump --schema-only` and the only reference in the output to
search_path is:
SELECT pg_catalog.set_config('search_path', '', false);
On 7/8/2019 11:48 PM, Igal @ Lucee.org wrote:
I have a custom search_path:
# show search_path;
search_path
--
"staging, transient, pg_catalog"
(1 row)
I ran `pg_dump --schema-only` and the only reference in the output to
search_path is:
On 7/9/2019 7:02 AM, Adrian Klaver wrote:
On 7/8/19 11:48 PM, Igal @ Lucee.org wrote:
I have a custom search_path:
# show search_path;
search_path
--
"staging, transient, pg_catalog"
(1 row)
I ran `pg_dump --schema-only` and the only re
David,
On 7/9/2019 7:49 AM, David G. Johnston wrote:
On Tue, Jul 9, 2019 at 7:41 AM Igal @ Lucee.org <mailto:i...@lucee.org>> wrote:
search_path is not set int he config, but rather with ALTER
DATABASE SET
search_path TO ... but I have executed that prior to the RESTORE
On 7/9/2019 10:45 AM, Adrian Klaver wrote:
On 7/9/19 7:41 AM, Igal @ Lucee.org wrote:
On 7/9/2019 7:02 AM, Adrian Klaver wrote:
On 7/8/19 11:48 PM, Igal @ Lucee.org wrote:
I have a custom search_path:
# show search_path;
search_path
--
"st
I am trying to change a text column into a numeric one in a large table.
My idea was to add a new column, update it, drop the old column, and
rename the new one to the old name. I am hoping that that would make it
faster and minimize locking time though I'm not sure that it would.
I am there
Thank you, David.
I should get more sleep...
Igal
On 7/31/2019 11:52 AM, David G. Johnston wrote:
On Wed, Jul 31, 2019 at 11:38 AM Igal @ Lucee.org <mailto:i...@lucee.org>> wrote:
alter table some_table
rename column amount_num to amount_text;
al
I have the following statement in a trigger:
new.email = lower(new.email);
When I try to update a record without setting the email column however,
I get an error:
SQL Error [42703]: ERROR: record "new" has no field "email"
Where: SQL statement "SELECT lower(new.email)"
PL/pgSQL function
On 8/5/2019 11:34 AM, Michael Lewis wrote:
As a note to the original poster, you might want to check out-
https://www.postgresql.org/docs/current/citext.html
Thanks, Michael. I'm familiar with the citext module.
There is no reason, however, for an email address to be not-lower-cased,
so whi
I have installed the CentOS 7 PGDG file from
https://yum.postgresql.org/repopackages.php
If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:
# PGDG Red Hat Enterprise Linux / CentOS / Scientific Linux stable
repositories:
[pgdg12]
name=PostgreSQL 12 $releasever - $basearch
baseurl=https:
I have installed the CentOS 7 PGDG file from
https://yum.postgresql.org/repopackages.php
If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:
# PGDG Red Hat Enterprise Linux / CentOS / Scientific Linux stable
repositories:
[pgdg12]
name=PostgreSQL 12 $releasever - $basearch
baseurl=https:
Adrian,
On 8/22/2019 12:22 PM, Adrian Klaver wrote:
On 8/22/19 11:29 AM, Igal @ Lucee.org wrote:
I have installed the CentOS 7 PGDG file from
https://yum.postgresql.org/repopackages.php
If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:
# PGDG Red Hat Enterprise Linux / CentOS
Adrian,
On 8/22/2019 12:48 PM, Adrian Klaver wrote:
In addition to my previous post maybe change:
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch
to:
baseurl=https://download.postgresql.org/pub/repos/testing/yum/12/redhat/rhel-$releasever-$basear
I have a data directory that was created by Postgres 12 (I thought beta
3 but now am not sure anymore) running in Docker.
I have installed Postgres 12b3 as a Systemd service and am trying to set
the cluster to the same PGDATA. I have set the owner of the directory
to postgres:postrgres, and t
Adrian,
On 8/22/2019 5:08 PM, Adrian Klaver wrote:
On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
I have a data directory that was created by Postgres 12 (I thought
beta 3 but now am not sure anymore) running in Docker.
I have installed Postgres 12b3 as a Systemd service and am trying to
set
On 8/22/2019 5:08 PM, Adrian Klaver wrote:
On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
I have a data directory that was created by Postgres 12 (I thought
beta 3 but now am not sure anymore) running in Docker.
I have installed Postgres 12b3 as a Systemd service and am trying to
set the
On 8/22/2019 9:15 PM, Tom Lane wrote:
Adrian Klaver writes:
On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
How can I start up the Cluster?
Is version 201906161 beta 2?
Yes:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/catversion.h;h
On 10/9/2019 12:34 AM, Laurenz Albe wrote:
Igal Sapir wrote:
I am trying to test a simple case insensitive comparison. Most likely the
collation that I chose is wrong, but I'm not sure how to choose the correct
one (for English/US?). Here is my snippet:
create collation case_insensitive(
Thomas,
On 10/10/2019 6:22 AM, Thomas Kellerer wrote:
Igal @ Lucee.org schrieb am 10.10.2019 um 14:41:
Thank you all for replying. I tried to use the locale suggested by
both Laurenz and Pavel, en-US-u-ks-level2, but I'm still getting
false for a simple comparison of 'Abc' =
If a function select data from a table, and the rows in the table may
change, would that function qualify for Immutable or does it have to be
Stable? I'm asking because according to the docs [1]: "An IMMUTABLE
function cannot modify the database and is guaranteed to return the same
results giv
On 6/24/2019 7:38 PM, David G. Johnston wrote:
On Mon, Jun 24, 2019 at 7:31 PM Igal @ Lucee.org <mailto:i...@lucee.org>> wrote:
If a function select data from a table, and the rows in the table may
change, would that function qualify for Immutable or does it have
to be
I have a trigger that is created like so:
create trigger tr_on_table_modified after insert or delete or update
on some_table for each row execute procedure on_table_modified();
CREATE OR REPLACE FUNCTION on_table_modified() RETURNS trigger
LANGUAGE plpgsql $$
/* some cod
On 6/28/2019 8:07 PM, David Rowley wrote:
On Sat, 29 Jun 2019 at 14:54, Igal @ Lucee.org wrote:
create trigger tr_on_table_modified after insert or delete or update
on some_table for each row execute procedure on_table_modified();
I can see in the output the notices with the
Hello,
I want to create three (group) roles. The first one will be read-only,
the second will add INSERT, and the third will add UPDATE and DELETE.
Does the below look OK for this purpose or did I forget something?
/** role_r is read-only with SELECT and EXECUTE */
CREATE ROLE role_r;
GRAN
On 12/24/2017 10:18 AM, Andreas Kretschmer wrote:
On 24 December 2017 18:52:39 CET, Michelle Konzack
wrote:
I try to convert a Database scheme from mySQL to pgSQL and have
problems with the line:
KEY post_date (post_date)
and later probably with the lines
UNIQUE KEY user
Michelle,
On 12/24/2017 1:20 PM, Michelle Konzack wrote:
Hello Igal
Am 2017-12-24 hackte Igal @ Lucee.org in die Tasten:
I am doing a similar thing migrating from SQL Server, and I am
considering to publish an open source Migrator.
Can you tell me about some of the data types that you had to
I am trying to run a query which was working a few weeks ago, and it
utilizes the <<-> operator but I am getting an error: operator does not
exist text <<-> text.
If I try instead the function word_similarity() I get a similar (no pun
intended) error: function word_similarity(text, text) does
On 1/28/2018 12:35 PM, Tom Lane wrote:
"Igal @ Lucee.org" writes:
I am trying to run a query which was working a few weeks ago, and it
utilizes the <<-> operator but I am getting an error: operator does not
exist text <<-> text.
If I try to install the pg_trgm exte
I want to use pg_trgm for auto-suggest functionality. I created a
Materialized View with the information that I need, with the relevant
columns being (keywords text, rank int). keywords is the column from
which I build the tri-grams, and rank is some popularity factor so that
popular results
Is there a way to consider white space in tri-grams? That would allow
for better matches of phrases.
For example, currently "one two three" and "three two one" would
generate the same tri-grams ({ o, t, on, th, tw,ee ,hre,ne
,one,ree,thr,two,wo }), and the distance of "one two four" will be
Is there a rational reason why Day of the Week is 0-based, i.e. Sunday
(0) to Saturday (6) instead of the more intuitive Sunday (1) to Saturday
(7)?
SELECT date_part('dow', current_date);
https://www.postgresql.org/docs/current/static/functions-datetime.html
Thanks,
Igal Sapir
Lucee Core
34 matches
Mail list logo
