SQL SERVER migration to PostgreSql

[İlyas Derse]

I'm trying to migration to PostgreSql from SQL Server. I have Stored
Procedures what have output parameters and returning tables.But you know
what, we can not returning tables in stored procedures in PostgreSql and we
can not use output parameters in functions in PostgreSql.

So i did not find to solves this problem. Anybody have an idea ?

INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION

[İlyas Derse]

I'm trying to migration to PostgreSql from SQL Server.  I have Stored
Procedures what have output parameters and return tables. How can i do both
together.

Its like ;

CREATE or replace FUNCTION public."test" (INOUT "x" integer, INOUT "y"
character varying(36))

RETURNS TABLE  (
"id" integer,
"filesize" character varying(36)
)
AS $$
 BEGIN
  x=6;
RETURN QUERY
SELECT * FROMpublic."tbl_employees" ;

END;
$$ LANGUAGE plpgsql;

I can not create that because of inout parameters.
Another place;

do $$
DECLARE b integer = 1;
DECLARE d integer = 2 ;
BEGIN
  select * from public."test"();
END;
$$;

 Anybody have an idea ?

Return Table in StoredProceure/Function

[İlyas Derse]

How can I return table in Stored Procedure ? I can do it in function but I
have inout parameters.So I can not create in function. What can I do this
case ?

I guess,It should be like for function :

CREATE or REPLACE FUNCTION public."test" (INOUT "x" integer, INOUT "y" text)

RETURNS TABLE  ("id" integer,"filesize" character varying(36))AS $$
 BEGINRETURN QUERYSELECT * FROMpublic."tbl_employees" ;
END;$$ LANGUAGE plpgsql;

Thanks.

Call Stored Procedure with inout parameters in c#

[İlyas Derse]

I tried with inout parameters but it did not work correctly.

here is my procedure :

CREATE OR REPLACE PROCEDURE public.testing ( INOUT x int )
LANGUAGE 'plpgsql'
AS $$
BEGIN
   x := x * 3;
END ;
$$;

my C# code:

public void myMethod2()
{
CRUD.con.Open();
int a = 5;
using (var cmd = new NpgsqlCommand("call public.testing();",
CRUD.con))
{
var objParam = new NpgsqlParameter("x",
NpgsqlDbType.Integer)
  { Direction = ParameterDirection.Output };
objParam.Value = 5;
cmd.Parameters.Add(objParam);
cmd.ExecuteNonQuery();
Console.WriteLine(objParam.Value);
}
CRUD.con.Dispose();
}

Insert Table from Execute String Query

[İlyas Derse]

I need to insert temp table from execute string query. How can I do ? I'm
trying like that but not working.

CREATE OR REPLACE FUNCTION public.testdyn
(
x integer
)
RETURNS TABLE
(
id bigint,
text character varying(4000)
)
AS $$
DECLARE mysql TEXT;
BEGIN
create temp table tmp1
(
id1 bigint,
text character varying(4000)
);
mysql = 'select id, text from TEST';

RETURN QUERY
  EXECUTE mysql INTO tmp1 ;
END;
$$ LANGUAGE plpgsql;

Is there an equivalent to sp_getapplock, sp_releaseapplock in PostgreSql?

[İlyas Derse]

In MSSQL I am using sp_getapplock, sp_releaseapplock.

How can I achieve the same functionality in PostgreSql?

How can I set a timeout for a locked table in Function ?

[İlyas Derse]

CREATE OR REPLACE FUNCTION public."testlock"()
RETURNS TABLE
(
 id integer,
 name character varying,
 state integer,
 owner character varying
)
LANGUAGE 'plpgsql'
AS $BODY$   
 BEGIN  
SET  "statement_timeout" = 6000; --- It's not changing. !!
LOCK TABLE public."lock" IN ROW EXCLUSIVE MODE; 
UPDATE public."lock" as l set name = 'deneme' 
WHERE l."id" = 4; 

RETURN QUERY
select l."id",l."name",l."state",l."owner" from 
public."lock" as l,
pg_sleep(10) where l."id" = 4;
END;
$BODY$;

select * from public."testlock"();

How can I do ?

Writing Postgres Extensions in C on Windows

[İlyas Derse]

I want to register C code to PostgreSql on Windows. So I think, I have to
make a extension for PostgreSql. But I did not find to written extension on
windows. Do you have an idea ?

Thanks ...

Re: Writing Postgres Extensions in C on Windows

[İlyas Derse]

Thanks, I'll check it out.

Thomas Munro , 7 Oca 2020 Sal, 23:56 tarihinde şunu
yazdı:

> On Wed, Jan 8, 2020 at 4:32 AM İlyas Derse  wrote:
> > I want to register C code to PostgreSql on Windows. So I think, I have
> to make a extension for PostgreSql. But I did not find to written extension
> on windows. Do you have an idea ?
>
> I don't do Windows myself but this blog from Craig Ringer looks like a
> good starting point:
>
>
> https://www.2ndquadrant.com/en/blog/compiling-postgresql-extensions-visual-studio-windows/
>

WaitForMultipleObjects in C Extension

[İlyas Derse]

 Hi guys I need your experiences. I made an Extension in C. When I stop the
query on PostgreSQL,I want to control my extension's result with use cases
in C.So I think , I should use WaitForMultipleObjects. Is there a like a
SignalObjectHandle() ? By the way I'm using Windows.

Thanks...

Is there a GoTo ?

[İlyas Derse]

In this function I have to GOTO to a label1, but GOTO keyword is not
working, can you please help me in getting the way from which I am able to
jump from a particular code to label.
 Thanks...

CREATE OR REPLACE FUNCTION test(i integer) RETURNS integer AS
$$BEGINIF i<0 THEN RETURN i + 1;ELSE
  GOTO label1;END IF<>RETURN null;END;$$ LANGUAGE plpgsql;

@@TRANCOUNT ?

[İlyas Derse]

What's Prostgres' equivalent of select @@trancount
?  Do you have an
idea ?

Force Commit

[İlyas Derse]

I'm writing to you about  Commit. I want to do force commit query even if
I have exception.
It's like :

CREATE OR REPLACE PROCEDURE public."test"()
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE "a" integer  ;
DECLARE "b" integer  ;
BEGIN
"a" = 1;
"b" = 0;
   BEGIN
   raise notice 'hata';
   update public."crud" set lastname = 'Tekindor' where autoid = 20;
   "a"="a"/"b";
   ROLLBACK;

  EXCEPTION
  WHEN OTHERS THEN

   COMMIT;
  END ;
END ;
$BODY$;

How can I do force commit  ?
Thanks..