Re: Porting from MSSQL Server

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');