[SQL] SQL Question

[Igor Kryltsov]

Hi,


If anybody can offer better SQL query please.
We have table
create table group_facility (
group_id integer not null,
facility_id integer not null
)
It stores facilities membership in group. For example: "North Region" -
facilityA, facilityB
I need to extract groups from this table which contain facilityN AND
facilityZ and may be others but these two(both) has to be a group member.

Query:
SELECT DISTINCT group_id FROM facility_group s1
WHERE EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id =
s1.group_id AND facility_id = 390)
AND
EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND
facility_id = 999)

works but what if I need to find groups where membership is (facilityN1,
facilityN100)??

Thank you,


Igor K



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Postgresql FK to MS SQL triggers

[Igor Kryltsov]

Hi,


If you are moving from Postgres to MS SQL you will most likely will find
that you can not recreate your PostgreSQL FK to MSSQL FK because this
enterprise class database will NOT allow you to create all 3 FK which are
exist in your PGSQL:

table users(user_id PK)

table journal(created_by, modified_by, deleted_by)

ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES
users(user_id) MATCH FULL ON UPDATE
 CASCADE ON DELETE CASCADE;
ADD CONSTRAINT fk_modified_by FOREIGN KEY (modified_by) REFERENCES
users(user_id) MATCH FULL ON UPDATE
 CASCADE ON DELETE CASCADE;
ADD CONSTRAINT fk_deleted_by FOREIGN KEY (deleted_by) REFERENCES
users(user_id) MATCH FULL ON UPDATE
 CASCADE ON DELETE CASCADE;


For interested people I wrote a PHP script which:
1) Extracts all underlying triggers from pg_trigger table  in Postgres used
to support FK (3 triggers for each FK)
2) Generates a MSSQL script file which recreates all triggers in MSSQL

Hope it will save some time for somebody.

Igor




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[SQL] Update PostgreSQL from MS SQL trigger

[Igor Kryltsov]

Hi,

I have table 'test_m'(id integer) in MSSQL and I want to write
on_test_m_insert trigger in MS SQL which will insert value into PostgreSQL
table 'test_p' from database 'test_db' running on host '10.3.2.5'.

Can this be achieved with PostgreSQL ODBC driver? If yes, please post
template of such trigger.


Thank you,


Igor



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Update PostgreSQL from MS SQL trigger

[Igor Kryltsov]

I managed to link PostgreSQL to MSSQL as a linked server but:

select * from [TEST].[test].[public].[users] << wrote in message
news:[EMAIL PROTECTED]
> Hi,
>
> I have table 'test_m'(id integer) in MSSQL and I want to write
> on_test_m_insert trigger in MS SQL which will insert value into PostgreSQL
> table 'test_p' from database 'test_db' running on host '10.3.2.5'.
>
> Can this be achieved with PostgreSQL ODBC driver? If yes, please post
> template of such trigger.
>
>
> Thank you,
>
>
> Igor
>
>



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Help to drop table

[Igor Kryltsov]

Please help to drop table with soace inside name.


  List of relations
 Schema | Name | Type  |  Owner
+--+---+--
 public | Facility Info| table | postgres



> DROP TABLE ??


Thank you,


Igor



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Help to drop table

[Igor Kryltsov]

DROP TABLE "Facility Info"

Thank you,


Igor



"Michael Fuhr" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Fri, Apr 22, 2005 at 03:24:10PM +1000, Igor Kryltsov wrote:
> >
> > Please help to drop table with soace inside name.
> >
> >
> >   List of relations
> >  Schema | Name | Type  |  Owner
> > +--+---+--
> >  public | Facility Info| table | postgres
> >
> > > DROP TABLE ??
>
> See "Identifiers and Key Words" in the "SQL Syntax" chapter of the
> documentation, in particular the discussion of quoted identifiers.
>
> -- 
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq