Hi,

I need to buid a SELECT statement for a software designed to make a simple
request, like "SELECT value FROM table WHERE arg = 'argument'"

My Problem is the request I wish to write is very complex : select from
table A then if there is no results from table A then select from table B

I think I have to write a stored procedure in PL/pgsql but I'm not familiar
with PL/sql nor PL/pgsql (only know TransacSQL from Microsoft...) and I
don't know how to do...

Thanks for any suggestions !

--
Rico

On Feb 21, 9:27 am, Eric Belhomme <{rico}+no/s... (AT) ricospirit (DOT) net>
wrote:

I myself come from a long-time MS SQL Server background; so, I
understand your pain. Though this is not exactly what you requested,
it should give you a very good start:


CREATE OR REPLACE FUNCTION InsertTransaction(checknumber int4, month
int4, day int4, year int4, CompanyName text, citystate text,
TypeOfTransaction text, amount numeric(10,2))
RETURNS text AS
$$
DECLARE MAXID INT;
PREVIOUSBALANCE NUMERIC(20,2);
COMPANY_ID INT;
CITY_STATE_ID INT;
TRANSACTIONTYPE_ID INT;
BEGIN
MAXID := (SELECT MAX(ID) FROM TRANSACTIONSTABLE);
PREVIOUSBALANCE := (SELECT BALANCE FROM TRANSACTIONSTABLE WHERE ID =
MAXID);
COMPANY_ID := (SELECT ID FROM COMPANYTABLE WHERE COMPANY =
CompanyName);
CITY_STATE_ID := (SELECT ID FROM LOCATIONTABLE WHERE LOCATION =
CityState);
TRANSACTIONTYPE_ID := (SELECT ID FROM TRANSACTIONTYPETABLE WHERE
TRANSACTIONTYPE = TypeOfTransaction);

IF (MAXID IS NULL) THEN
MAXID := 0;
END IF;

IF (PREVIOUSBALANCE IS NULL) THEN
PREVIOUSBALANCE := 0;
END IF;

IF (TypeOfTransaction = 'D') THEN
INSERT INTO TRANSACTIONSTABLE VALUES ((MAXID + 1),
CheckNumber, CAST((MONTH || '/' || DAY || '/' || YEAR) AS DATE),
COMPANY_ID, CITY_STATE_ID, TRANSACTIONTYPE_ID, 0, AMOUNT,
(PREVIOUSBALANCE + AMOUNT));
ELSE
INSERT INTO TRANSACTIONSTABLE VALUES ((MAXID + 1),
CheckNumber, CAST((MONTH || '/' || DAY || '/' || YEAR) AS DATE),
COMPANY_ID, CITY_STATE_ID, TRANSACTIONTYPE_ID, AMOUNT, 0,
(PREVIOUSBALANCE - AMOUNT));
END IF;

RETURN 'Entry was successful. Your new balance is: ' ||
(SELECT BALANCE FROM TRANSACTIONSTABLE WHERE ID = (SELECT MAX(ID) FROM
TRANSACTIONSTABLE));
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION inserttransaction(checknumber int4, "month" int4, "day"
int4, "year" int4, company text, citystate text, transactiontype text,
amount numeric(10,2)) OWNER TO postgres;

----------------------------------------------------
Ofcourse, the main difference would be adding something like: if
((select count(*) from TableA...) = 0) then select * from TableB...;
else select * from TableA...; end if;

Hope this is helpful.

Regards,

Enrique Martinez
Sr. Web/Database Developer

EMartinez <emartinez.pr1 (AT) gmail (DOT) com> wrote:

Hmm - I don't think that this is what the original poster wanted...

I assume that tables A and B have identical structure.

Then something like the following does SELECT * FROM a, and when there are
no results, then SELECT * FROM b.

CREATE OR REPLACE FUNCTION select_a_or_b() RETURNS SETOF a
LANGUAGE plpgsql STABLE STRICT AS
$$DECLARE
a_row a%ROWTYPE;
a_found boolean := FALSE;
BEGIN
FOR a_row IN SELECT * FROM a LOOP
a_found := TRUE;
RETURN NEXT a_row;
END LOOP;
IF a_found THEN RETURN; END IF;
FOR a_row IN SELECT * FROM b LOOP
RETURN NEXT a_row;
END LOOP;
END;$$;

This is just a quick example, I do not claim that it is perfect or even
correct, but it might give an idea how to go about it.

Yours,
Laurenz Albe