Create function in PostgreSQL-CodePudding

I am not sure how the CREATE FUNCTION statement works in PostgreSQL. I want to define a function (just for entertainment) such that given a number n, it prints asterisks starting from 1 up to n So I wrote this:

CREATE FUNCTION asterisks(n int)
RETURNS CHAR AS
BEGIN
for i in range(1,n 1):
   print("*"*i   "\n")
END
LANGUAGE python

The result I want for n=3:

*
**
***

However, I am not sure if calling Python like that is possible. I've read that Postgres supports Python as a procedural language in here:

https://www.postgresql.org/docs/current/xplang.html

CodePudding user response：

Postgres 14 or later

The simplest way would be with the new standard SQL syntax:

CREATE OR REPLACE FUNCTION asterisks(n int)
  RETURNS SETOF text
RETURN repeat('*', generate_series (1, n));

Or better (and all standard SQL):

CREATE OR REPLACE FUNCTION asterisks(n int)
  RETURNS SETOF text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
BEGIN ATOMIC
SELECT repeat('*', g) FROM generate_series (1, n) g;
END;

"Better" because it's easier to understand, sticks to standard SQL (more portable). Both debatable. And it sets IMMUTABLE STRICT PARALLEL SAFE appropriately, which would otherwise default to VOLATILE CALLED ON NULL INPUT PARALLEL UNSAFE. Non-debatable.

Call:

SELECT asterisks(6);

Or, more explicitly and standard-conforming:

SELECT * FROM asterisks(6);

See:

Postgres 13 (or any version):

SQL function:

CREATE OR REPLACE FUNCTION asterisks(n int)
  RETURNS SETOF text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT repeat('*', generate_series (1, n));
$func$;

PL/pgSQL function with loops (looping is typically more expensive):

CREATE OR REPLACE FUNCTION pg_temp.asterisks(n int)
  RETURNS SETOF text
  LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
BEGIN
FOR i IN 1..n LOOP
   RETURN NEXT repeat('*', i);
END LOOP;
END
$func$;

See:

Difference between language sql and language plpgsql in PostgreSQL functions

Of course, for the simple example, I would just run the plain statement instead of creating a function:

SELECT repeat('*', generate_series (1, 3));