1

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Mangostino
  • 157
  • 1
  • 6
  • 1
    There is PL/Python, typically used as "untrusted" version (`LANGUAGE plpythonu`). Be sure to read the current manual (or the one for your Postgres version), 9.4 is outdated. https://www.postgresql.org/docs/current/plpython.html But `LANGUAGE sql` and `LANGUAGE plpgsql` are much more commonly used. https://stackoverflow.com/a/24771561/939860 Are you asking about PL/Python, or just the best way to implement your function? – Erwin Brandstetter Oct 11 '21 at 00:16
  • Thank you. If you could give me the best way to implement the function i'll appreciate it. – Mangostino Oct 11 '21 at 00:24
  • Print? Why (and whereto) would you want to print from Postgres?! Write a function that returns text instead. – Bergi Oct 11 '21 at 00:26

1 Answers1

5

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:

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

SELECT repeat('*', generate_series (1, 3));
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228