Thread: Porting from MSSQL Server

I am porting a database from M$SQL Server to PostgreSQL.

I have a problem with a query. On MS SQL Server it looks like this:

DECLARE @handle AS NVARCHAR(40)
EXEC @handle = IPAddressToHandle 'x.x.x.x'
SELECT * FROM tblEmployee WHERE tblEmployee.handle = @handle UNION SELECT
...

I have created the function  IPAddressToHandle which works, but how do I
declare the variable handle, execute the function and then the query with
the variable as parameter.

Btw.: I cannot resolve the ip address to the handle with INNER JOINS in the
query since the query is VERY long with many UNION SELECT statements.

/Hans

_________________________________________________________________
F� gode tilbud direkte i din mailbox http://jatak.msn.dk

On Sun, 24 Aug 2003, Hans Jorgensen wrote:

> I am porting a database from M$SQL Server to PostgreSQL.
>
> I have a problem with a query. On MS SQL Server it looks like this:
>
> DECLARE @handle AS NVARCHAR(40)
> EXEC @handle = IPAddressToHandle 'x.x.x.x'
> SELECT * FROM tblEmployee WHERE tblEmployee.handle = @handle UNION SELECT
> ...
>
> I have created the function  IPAddressToHandle which works, but how do I
> declare the variable handle, execute the function and then the query with
> the variable as parameter.

I think you'll need to write an appropriate function (unless you're doing
this from psql).  If you want the output from the select (as opposed to
doing something else with it, it might be as simple as something like:

CREATE OR REPLACE FUNCTION foo(text) returns setof tblEmployee as '
DECLARE
 handlevar varchar(40);
 r record;
BEGIN
 handlevar := IPAddressToHandle($1);
 FOR r IN SELECT * from tblEmployee where handle=handlevar
  UNION ...
   RETURN NEXT r;
 END LOOP;
 RETURN;
END;' language 'plpgsql';

select * from foo('x.x.x.x');