[SQL] Time penalty on VIEWS on VIEWS

[hubert depesz lubaczewski]

Hi,

My situation: VIEW A is grouping information from 2 tables (B,C). I have to
make another VIEW with additional WHERE statement compared in VIEW A. I can
make this as a VIEW groupping tables B and C, or makeing VIEW of VIEW A.
I know that second sollution is more flexible but there should be some kind of
time penalty. I'm wondering how much it is.

regards,

depesz


-- 
hubert depesz lubaczewski

 najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo,
  jest niesamowita wręcz łatwość unikania kontaktów z nim ...

[SQL] Question about ordering views

[hubert depesz lubaczewski]

Hi,

I'm wondering if it is possible to make view (or procedure or triger or rule or
something) to force SELECTs to this view (rule, procedure or something) to
force always using predefined ORDER BY statement without removing possibility
to use WHERE statement to this SELECT. 

regards,

depesz

-- 
hubert depesz lubaczewski

 najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo,

Re: [SQL] Conditional SQL query

[Indraneel Majumdar]

On Sun, 5 Nov 2000, Marten Feldtmann wrote:

> Indraneel Majumdar schrieb:
> > 
> > Hi,
> > 
> > how can I insert a record after checking whether one of the fields has a
> > certain value or not? eg:
> > 
> > if table(col1)='value' insert into table values (x,x,x)
> > 
> > I am using perl interface. Is there any way to define a function to take
> > table name as an argument? (eg: select * from $table) I tried but this
> > gave an error (so I'm using perl to pass the real tablename for each
> > query)
> > 
> 
> insert into table select ... where col1 = 'value'
> 

this is working but I am inserting values directly and not by select. eg:

insert into table values(x,x,x)
and not
insert into table select * from table2

the former is not working.

\Indraneel

> 
> MF
> 

/.
# Indraneel Majumdar  ¡  E-mail: [EMAIL PROTECTED]  #
# Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
# Centre for DNA Fingerprinting and Diagnostics, #
# Hyderabad, India - 500076  #
`/

Re: [SQL] Conditional SQL query

[Cristóvão B. B. Dalla Costa]

> > insert into table select ... where col1 = 'value'
> > 
> 
> this is working but I am inserting values directly and not by select. eg:
> 
> insert into table values(x,x,x)
> and not
> insert into table select * from table2
> 

insert into table select (x, x, x) where col1 = 'value'

Re: [SQL] Conditional SQL query

[Indraneel Majumdar]

I finally got it to work. you can't use the parentheses. eg:

insert into table select x,x,x where col1 = 'value';
and not
insert into table select (x,x,x) where col1 = 'value';

the latter gives a
ERROR:  parser: parse error at or near "where"
I am using PostgreSQL-7.0.2

Now I have one more problem. How do I insert only once if value does not
exist? eg:

insert into table once select x,x,x where not col1='value'

Thanks,
Indraneel

On Sun, 5 Nov 2000, Cristóvão B. B. Dalla Costa wrote:

> > > insert into table select ... where col1 = 'value'
> > > 
> > 
> > this is working but I am inserting values directly and not by select. eg:
> > 
> > insert into table values(x,x,x)
> > and not
> > insert into table select * from table2
> > 
> 
> insert into table select (x, x, x) where col1 = 'value'
> 

/.
# Indraneel Majumdar  ¡  E-mail: [EMAIL PROTECTED]  #
# Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
# Centre for DNA Fingerprinting and Diagnostics, #
# Hyderabad, India - 500076  #
`/

Re: [SQL] Conditional SQL query

[Cristóvão B. B. Dalla Costa]

>
> Now I have one more problem. How do I insert only once if value does not
> exist? eg:
>

You should create a unique index on the appropriate fields. For example:

CREATE UNIQUE INDEX indexname ON table (field1, field2)

Then the insert will fail if this particular combination of the values of
(field1, field2) is already in the table.

Be careful if the insert is inside a transaction, tough. If it fails due to
non-uniqueness your transaction will be aborted (all further queries ignored
untill a COMMIT or ROLLBACK).

Re: [SQL] Conditional SQL query

[Indraneel Majumdar]

OK, I solved my own problems (Congrats ;-) Sorry for bothering the list.
This is what I'm doing:

INSERT INTO table SELECT x,x,x where (select count(*) from table where
col1='value')=0;

any suggestions?

thanks,
Indraneel

On Mon, 6 Nov 2000, Indraneel Majumdar wrote:

> Now I have one more problem. How do I insert only once if value does not
> exist? eg:
> 
> insert into table once select x,x,x where not col1='value'
> 

/.
# Indraneel Majumdar  ¡  E-mail: [EMAIL PROTECTED]  #
# Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
# Centre for DNA Fingerprinting and Diagnostics, #
# Hyderabad, India - 500076  #
`/