Create a function in PostgreSQL

Question

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

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. postgresql.org/docs/current/plpython.html But LANGUAGE sql and LANGUAGE plpgsql are much more commonly used. stackoverflow.com/a/24771561/939860 Are you asking about PL/Python, or just the best way to implement your function? — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 11, 2021 at 0:16
Thank you. If you could give me the best way to implement the function i'll appreciate it. — Mangostino
– Mangostino, Commented Oct 11, 2021 at 0:24
Print? Why (and whereto) would you want to print from Postgres?! Write a function that returns text instead. — Bergi
– Bergi, Commented Oct 11, 2021 at 0:26

Erwin Brandstetter · Accepted Answer · 2021-10-11 00:52:47Z

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));

I would've thought the OP expects SELECT string_agg(repeat('*', g), '\n') FROM generate_series (1, n) g
@Bergi: Maybe. Looks like the OP just wanted syntax templates for basic functions.
Why is the second (more verbose) form better than the first one?
@AdamPiotrowski: OK, "better" is fuzzy. I added explanation.

blackgreen · Accepted Answer · 2024-02-19 11:29:12Z

You can create a function in PL/pgSQL or SQL language.

For example, you create test table as shown below:

CREATE TABLE test (
  num INTEGER
);

Then, you insert the row whose num is 2 as shown below:

INSERT INTO test (num) VALUES (2);

Now, you can create my_func() PL/pgSQL function which adds value to num and returns value to the caller as shown below:

CREATE FUNCTION my_func(value INTEGER) RETURNS INTEGER
AS $$
BEGIN
  UPDATE test set num = num + value;
  SELECT num INTO value FROM test;
  RETURN value;
END;
$$ LANGUAGE plpgsql;

Or, you can create my_func() SQL function which adds value to num and returns value to the caller as shown below:

CREATE FUNCTION my_func(value INTEGER) RETURNS INTEGER
AS $$
UPDATE test SET num = num + value;
SELECT num FROM test;
$$ LANGUAGE SQL;

Then, you can call my_func(3) with SELECT statement, then 5 is returned and 3 is added to num as shown below:

postgres=# SELECT my_func(3);
 my_func
---------
       5
(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

Collectives™ on Stack Overflow

Create a function in PostgreSQL

2 Answers 2

Postgres 14 or later

Postgres 13 (or any version):

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Postgres 14 or later

Postgres 13 (or any version):

4 Comments

Comments

Linked

Related