CODEWITHSUNDEEP
postgresqlstored-procedures
wasim latheef
wasim latheef

Reputation: 5

From SQL Server to Postgres

I'm new to Postgresql; I have only used SQL Server before so I'm trying to migrate an ASP.NET MVC application to ASP.NET Core 3.0 and replace SQL Server with PostgreSQL in the process, and move to Ubuntu.

Can anyone tell what's wrong with my query here?

CREATE OR REPLACE PROCEDURE officekit.testsp (mode CHARACTER)
LANGUAGE plpgsql AS $plpgsql$
BEGIN
    IF mode = 'test' THEN
        SELECT a,b,c,d FROM testSchema.test;
    END IF;
    COMMIT;
END;
$plpgsql$;

Upvotes: 0

Views: 310

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45930

In your code almost every line is not good.

First if you want to write stored procedures in Postgres, please start with the documentation. The concept of stored procedures in MSSQL is significantly different to that in Oracle, DB2, and certainly different to that in Postgres. PLpgSQL in Postgres is similar to Oracle's PL/SQL. The best thing to do, is to forget all that you know about stored procedures and start from scratch.

The errors:

  1. procedures in Postgres cannot return data - only functions can do this - in your example you probably need a table function.

    CREATE OR REPLACE FUNCTION officekit.testsp(mode text)
RETURNS TABLE(a text, b text, c text)
AS $$
BEGIN
  IF mode = 'test' THEN
    RETURN QUERY SELECT test.a, test.b, test.c FROM test;
  END IF;
END;
$$ LANGUAGE plpgsql;

Personally I don't like this style of programming. Functions should not just wrap queries. A lot of significant performance problems and architecture issues arise from the bad use of this possibility, but it depends on context.

  1. Why is commit there? This code doesn't make any change in the database. There is no reason to call commit.

Upvotes: 1

Related Questions