Thread: left join

Is it just me, or is there no way to do a left join in postgresql?

Is there any way to do this, I have checked the documentation, looked
through the src, and there doesnt seem to be a reference to it

Any comments appreciated

>Is it just me, or is there no way to do a left join in postgresql?
>Is there any way to do this, I have checked the documentation, looked
>through the src, and there doesnt seem to be a reference to it
>Any comments appreciated

It's not you. :)   :(

IMHO, you could do somethink like:

create table t1 ( field_A_of_t1 int, field_Bref_of_t1 int);
create table t2 ( field_Aid_of_t2 int, field_B_of_t2 int);

select field_A_of_t1, field_B_of_t2
 from
    t1,t2
 where
    field_A_of_t1 = SOMEVALUE and
    field_Aid_of_t2 = field_Bref_of_t1
union
select field_A_of_t1, null
 from
    t1
 where
    field_A_of_t1 = SOMEVALUE and field_Bref_of_t1 is null
order by field_B_of_t2;


(I've just checked this - it works.)

Note - using somethink like
... and (field_Aid_of_t2=field_Bref_of_t1 or field_Bref_of_t1 is null)
will not work (you'ld have the product).

I would appreciate if someone verified this suggestion .

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
`````````````````````````````````````````````
Silvio Emanuel Nunes Barbosa de Macedo
mailto:smacedo@inescn.pt

INESC - Porto - Grupo CAV
Pc da Republica, 93 R/C   Tel:351 2 209 42 21
4000 PORTO  PORTUGAL      Fax:351 2 208 41 72

I have a table that among other things has a name, address, city, state
fields. When I insert into, I want to be able to make sure that there is
no duplicate records or that a row is inserted that is already in the DB.

Question number one is: Should I use a trigger or a rule?

And request number two is perhaps a sample that could get me started.

I've read thru the Documentation and Man pages and tried creating a rule
but, had no luck.

Thanks in advance.

Andy

Andy Lewis wrote:
>
> I have a table that among other things has a name, address, city, state
> fields. When I insert into, I want to be able to make sure that there is
> no duplicate records or that a row is inserted that is already in the DB.
>
> Question number one is: Should I use a trigger or a rule?
>
> And request number two is perhaps a sample that could get me started.
>
> I've read thru the Documentation and Man pages and tried creating a rule
> but, had no luck.
>

I know this isn't exactly what you want. I had a unique trigger in C,
but doing it in PL is much easier. Here is an example of a singleton --
i.e. a trigger that allows only one row in a table.


DROP FUNCTION singleton();
CREATE FUNCTION singleton () RETURNS opaque AS
'
DECLARE
BEGIN
   DELETE FROM daemon;
   RETURN new;
END;' LANGUAGE 'plpgsql';
DROP TRIGGER daemon_singleton ON daemon;
CREATE TRIGGER daemon_singleton BEFORE INSERT ON daemon
FOR EACH ROW EXECUTE PROCEDURE singleton('daemon');

The new row is always available in the variable 'new', so that you could
do something along the lines of

select count(*) into cnt from <tablename> where new.<key> = key;
if (cnt>0) then
    delete from <tablename> where key = new.<key>
end if

Remember to return new, leave spaces around the = comparisons, and
declare the variable cnt in the declare section (as int4 or something).

The documentation for PL is actually quite good, and you should also
have a look at the examples. You need to load PL as an interpreted
language, so you need something along the lines of

DROP FUNCTION plpgsql_call_handler();
CREATE FUNCTION plpgsql_call_handler() RETURNS opaque
        AS '<path to postgres>/lib/plpgsql.so'
        LANGUAGE 'C';

DROP PROCEDURAL LANGUAGE 'plpgsql';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
        HANDLER plpgsql_call_handler
        LANCOMPILER 'PL/pgSQL';

first.

Good luck, Adriaan

>
> select count(*) into cnt from <tablename> where new.<key> = key;
> if (cnt>0) then
>         delete from <tablename> where key = new.<key>
> end if
>

Just looked at this, and this is not actually what you wanted. If you do
not want to replace the old row, do something along the lines

RAISE EXCEPTION ''Duplicate entry''

which will abort the insert. It's all in the manual.

Adriaan