'syntax error at end of input' - while trying to launch stored procedure from DB

Question

I am writing in .net core and trying to launch stored procedure from database(Postgresql).

I am getting an error saying there is an "syntax error at end of input". Connection string is right(found out during debugging). The problem is with .FromSql() method syntax.

here is the code:

List<ActivePackageForOpenBillingPeriod> activeUserPackagesForOpenBillingPeriod =null;

        using(var conn = new NpgsqlConnection("Host=localhost;Port=xxx;Database=postgres;Username=xxx;Password=xxx;TrustServerCertificate=xxx;ApplicationName=xxx;"))
        {

            var s = "create function \"GetActiveUserPackagesForOpenBillingPeriod({0})\"";
            activeUserPackagesForOpenBillingPeriod = context.ActivePackageForOpenBillingPeriods.FromSql(s,DateTime.Now).ToList();
        }

This is the stored procedure I am trying to call

    create function "GetActiveUserPackagesForOpenBillingPeriod"(date 
timestamp without time zone) returns TABLE("Amount" numeric, "Package" 
character varying, "User" text,"Account" int, "AcceptanceActID" int, 
"HasChangedPackage" boolean)
    language plpgsql
as
$$
DECLARE
    BEGIN
      RETURN QUERY
        SELECT
            a."ID" AS "AccountId",
            u."ID" AS "UserId",
            up."PackageID",
            up."TotalAmount",
            CASE
                  WHEN count(DISTINCT tl."PackageID") IN (0,1)  THEN false
                  ELSE true
            END AS "HasChangedPackage"

        FROM public."Accounts" as a
            LEFT JOIN billing."TransactionHeaders" AS th ON th."AccountID" = a."ID"
            INNER JOIN security."Users" AS u ON u."AccountID"= a."ID"
            INNER JOIN billing."UserPackages" AS up ON up."UserID"=u."ID" AND COALESCE(th."Date", date) BETWEEN up."StartDate" AND COALESCE(up."EndDate", date)
            LEFT JOIN billing."TransactionLines" AS tl ON th."Id" = tl."TransactionHeaderID"

        WHERE th."AcceptanceActID" IS NULL AND a."StatusID"!=4 AND a."StatusID"!=3 AND up."StatusID"=1


        GROUP BY a."ID", u."ID", up."PackageID", up."TotalAmount";

END;
$$;

JGH · Accepted Answer · 2019-06-05 17:28:10Z

1

The query you are running is wrong. It is a mix between the function creation statement and a call to get its result.

Also only the function name should be doublequoted, not the parameter

Change the query to

var s = "SELECT * FROM \"GetActiveUserPackagesForOpenBillingPeriod\"({0})";

answered Jun 5, 2019 at 17:28

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Guga Todua Over a year ago

Did it. I have a different error now - PostgresException: 42883: function GetActiveUserPackagesForOpenBillingPeriod(timestamp without time zone) does not exist. That's probably parameter problem right?

JGH Over a year ago

@GugaTodua there could be multiple reasons why the function is not found. Was it created? Does the user calling it has the execute privilege etc. The parameter seems fine (it is the same type in the error message and in the function definition)

Guga Todua Over a year ago

I have already created a function and I do have an execute privilege. The problem most probably is with the parameter I am passing(DateTime.Now - which is "06/06/2019 15:06:55".

Jeremy · Accepted Answer · 2019-06-05 15:00:06Z

1

It looks like you are missing a RETURN; at the end of your function. It should be on the line above the END;

answered Jun 5, 2019 at 15:00

Jeremy

6,77322 silver badges21 bronze badges

Collectives™ on Stack Overflow

'syntax error at end of input' - while trying to launch stored procedure from DB

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related