Cast Const Integer to Bigint in Postgres

sql postgresql casting type-conversion

34,923

You've suddenly encountered the feature ) Record needs two and more fields. So when you have only one out variable, then result must be scalar.

So, you can simply do what compilers ask )

CREATE OR REPLACE FUNCTION GetNumberOne(
        OUT numberone bigint)
      RETURNS bigint AS
    $BODY$

        SELECT CAST(1 AS BIGINT) AS "NUMBERONE";

    $BODY$
      LANGUAGE sql VOLATILE;

plpgsql example:

CREATE OR REPLACE FUNCTION NumberOne()
      RETURNS bigint AS
    $BODY$
      DECLARE num bigint;
      BEGIN
        num := 1;
        RETURN num;
      END
    $BODY$
      LANGUAGE plpgsql VOLATILE;
      select * from NumberOne()

34,923

Author by

Nick Vaccaro

Notable Answers Very fun exercise: http://meta.stackexchange.com/questions/178894/how-to-avoid-users-on-stack-overflow Definitely done this myself: Counting values between two dates Notable Questions Still can't decide on this: SVN Repository Structure

Updated on March 21, 2020

Comments

Nick Vaccaro about 4 years
I'm getting the below error while running the below script. My goal is to create a function in Postgres to return 1 as a bigint. Help please!

hashtagpostgresnoobie

ERROR: function result type must be bigint because of OUT parameters
```
CREATE OR REPLACE FUNCTION GetNumberOne(
    OUT numberone bigint)
  RETURNS SETOF record AS
$BODY$

    SELECT CAST(1 AS BIGINT) AS "NUMBERONE";

$BODY$
  LANGUAGE sql VOLATILE;
```
Nick Vaccaro about 12 years

I apologize for my slowness, but could you please be more specific? I had a hard time following that. I've been using Postgres for all of about 10 hours now. :/
Timur Sadykov about 12 years

I'll provide exact snippet after lunch )
Timur Sadykov about 12 years

It turned out to be simpler. Take a look.
Nick Vaccaro about 12 years

Holy fantastic answers, Batman! I'd upvote more than once if I could.