On Tuesday 26 June 2007 22:50, Tom Lane wrote:
> Please provide an example, because the rewriter is most certainly
> applied to queries from functions.
>
> I suspect you are actually being burnt by some other effect, like a row
> disappearing from the view as soon as its underlying data is delete
Hi,
I have the following scenerio:
a rule on a view which executes a function by means of a select call, which in
turn deletes from a table which has on-delete rules on it. When the function
is called from the rule, the subsequent delete call in the function doesn't
cause the on-delete rules on t
On Wednesday 14 March 2007 22:59, Wiebe Cazemier wrote:
> My question was mainly general;
> if there is a better way than using subselects to join two tables which
> are only connected to eachother through a join-table (containing only
> references to the two tables in question). Su
On Thursday 15 March 2007 15:05, Rodrigo De León wrote:
> From http://www.postgresql.org/docs/8.2/static/indexes-types.html :
>
> "The optimizer can also use a B-tree index for queries involving the
> pattern matching operators LIKE and ~ if the pattern is a constant and
> is anchored to the begi
On Wednesday 14 March 2007 18:58, Frank Bax wrote:
> A performance question should always include the output of EXPLAIN ANALYZE.
>
> I think the problem is database design. If you added a boolean column into
> accounts table which would indicate owner/co-owner; then all data from
> account_co_own
Hi,
Doing a join on one-to-many relations (like "orders" joining "custumors") is
easy, but what if there are many-to-many relations involved?
Consider this scenario of three (simplified) tables:
people
- id
- name
accounts
- id
- owner_id REFERENCES people
account_co_owners
- co_owner_id REFER
On Wednesday 19 April 2006 19:21, Volkan YAZICI wrote:
> Here's simple query, to list which table's which columns references to a
> specific table. (Hope this is what you asked for.)
>
> SELECT TBL.table_name, COL.column_name
> FROM information_schema.referential_constraints AS REF
> INNE
On Wednesday 19 April 2006 17:00, Wiebe Cazemier wrote:
> Never mind. I found another solution.
Whoops. Posted to quickly there. I do still need to know the amount of
references to the primary key.
---(end of broadcast)---
TIP 1: if post
On Wednesday 19 April 2006 15:33, Wiebe Cazemier wrote:
> Either I'm blind, or this is not what I'm looking for. I need to know the
> amount of references _to_ a primary key. If you have a customer with two
> orders and one invoice, the reference count for the primary key of th
On Wednesday 19 April 2006 15:09, A. Kretschmer wrote:
> Yes,
> http://www.postgresql.org/docs/8.1/interactive/catalog-pg-constraint.html
Either I'm blind, or this is not what I'm looking for. I need to know the
amount of references _to_ a primary key. If you have a customer with two
orders and o
Hi,
I'm trying to find which of the tables in pg_catalog contains the amount of
references to a primary key, but I can't seem to find it. Google queries are
also less than successful. Is it even stored in the catalog?
---(end of broadcast)---
TIP
On 03/28/06 11:13, Maciej Piekielniak wrote:
>create or replace function uporzadkuj_reguly(text,text) RETURNS VOID AS
>'
>DECLARE
> licznik integer:=1;
>
> reguly CURSOR FOR SELECT * from firewall ORDER BY id_firewall WHERE
> tabela=$1 and lancuch=$2 for UPDATE;
>BEGIN
> for i in reguly LOOP
> U
On 03/28/06 01:35, Maciej Piekielniak wrote:
>Hello ,
>
>I try to translate my old functions from oracle but don't understand what is
>wrong.
>
>create or replace function uporzadkuj_reguly(text,text) RETURNS integer AS
>'
>DECLARE
> tabela ALIAS FOR $1;
> lancuch ALIAS FOR $2;
> ret integer:=0;
(Whoops, pressed wrong reply button. Here it is correctly this time.)
On 03/27/06 17:02, Tom Lane wrote:
>It'll retrieve whatever the current value of the plpgsql variable
>provider_id is. plpgsql always assumes that ambiguous names refer
>to its variables (indeed, it isn't even directly aware t
(Whoops, pressed wrong reply button. Here it is correctly this time.)
On 03/27/06 16:48, Jim C. Nasby wrote:
>
>Sadly, overloading variable names between plpgsql and SQL is *highly*
>problematic. Because of this I *always* prefix plpgsql variables with
>something, such as p_ for parameters and v_
Hi,
In a plpgsl function, consider the following excerpt:
DECLARE
provider_id INTEGER;
BEGIN
provider_id := (SELECT provider_id FROM investment_products WHERE id =
my_new.investment_product_id);
END;
After a lot of trouble, I found out this line doesn't work correctly
with the variable name
Tom Lane wrote:
So, the actual delete should be done after all the rules. And even if
it does delete before anything else, that does not explain why "step2"
is not inserted into the debuglog table.
Because the rule converts those inserts into, effectively,
INSERT INTO debuglog SELECT
Sarah Asmaels wrote:
Hi!
I have one table referencing an object in another table through an ID,
and a view joining those tables on the ID. I want to create rules to
rewrite updates/deletes/inserts on the joined view to act on the real
tables. Can you give me some pointers? The documentation has
Tom Lane wrote:
A quote from the postgresql manual:
"But for ON UPDATE and ON DELETE rules, the original query is done after the
actions added by rules. This ensures that the actions can see the to-be-updated
or to-be-deleted rows"
Yes, I know that quote. I wrote it. It's not relevant her
Tom Lane wrote:
> The rule that actually deletes the rows from the underlying has to fire
> last, since the rows are gone from the view (and hence from OLD) the
> moment you delete them.
A quote from the postgresql manual:
"But for ON UPDATE and ON DELETE rules, the original query is done after
Hi,
I've got the following table (contents not really relevant):
CREATE TABLE _rating_params (
id SERIAL PRIMARY KEY,
letter CHAR(1) NOT NULL CHECK (letter = 'E' OR letter = 'F'),
superparam_id INTEGER REFERENCES _rating_params,
seq_num INTEGER NOT NULL DEFAULT 1,
name_id INTEGER NOT N
21 matches
Mail list logo
