Function creation syntax in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the basic syntax to create a function in PostgreSQL?

Use <code>CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$ function_body $$ LANGUAGE language_name;</code> to define a function.

beginner

What keyword specifies the programming language used inside a PostgreSQL function?

The LANGUAGE keyword specifies the language, for example, LANGUAGE plpgsql or LANGUAGE sql.

beginner

How do you specify the return type of a PostgreSQL function?

Use the RETURNS clause followed by the data type, like RETURNS integer or RETURNS void.

intermediate

What is the purpose of the $$ symbols in PostgreSQL function creation?

They are dollar-quoting delimiters that mark the start and end of the function body, allowing you to include single quotes inside without escaping.

intermediate

Can a PostgreSQL function return a table? If yes, how is it declared?

Yes, by specifying RETURNS TABLE(column1 type1, column2 type2, ...) in the function signature.

Which keyword is used to start creating a function in PostgreSQL?

A CREATE FUNCTION

B DEFINE FUNCTION

C MAKE FUNCTION

D FUNCTION CREATE

The correct syntax starts with CREATE FUNCTION.

What does the RETURNS clause specify in a PostgreSQL function?

A The function's input parameters

B The function's language

C The function's return data type

D The function's name

The RETURNS clause defines the data type the function will return.

What is the purpose of the LANGUAGE clause in a PostgreSQL function?

A To specify the language of the operating system

B To specify the language of the database

C To specify the language of the client

D To specify the programming language used inside the function

It tells PostgreSQL which programming language the function code is written in.

Which of the following is a valid way to delimit the function body in PostgreSQL?

A Single quotes ' '

B Dollar quoting $$ $$

C Double quotes " "

D Backticks ` `

Dollar quoting with $$ allows including single quotes inside the function body without escaping.

How do you declare a function that returns multiple columns as a table in PostgreSQL?

A RETURNS TABLE(column1 type1, column2 type2)

B RETURNS MULTI

C RETURNS ARRAY

D RETURNS SETOF type

Use RETURNS TABLE with column definitions to return multiple columns.

Explain the basic syntax to create a function in PostgreSQL including how to specify parameters, return type, and language.

Think about the order and keywords used in the function creation statement.

Describe how you can create a PostgreSQL function that returns a table with multiple columns.

Focus on the RETURNS part and how to define multiple columns.

Practice

1. What is the purpose of the CREATE FUNCTION statement in PostgreSQL?

easy

A. To delete rows from a table

B. To create a new table in the database

C. To insert data into an existing table

D. To define a reusable block of code that can be called later

Solution

1. Step 1: Understand the role of functions in PostgreSQL

   Functions store reusable code inside the database to perform tasks repeatedly.

2. Step 2: Identify what CREATE FUNCTION does

   This statement defines a new function with parameters, return type, and body.

3. Final Answer:

   To define a reusable block of code that can be called later -> Option D

4. Quick Check:

   CREATE FUNCTION defines reusable code [OK]

Hint: Functions store reusable code blocks in the database [OK]

Common Mistakes:

• Confusing function creation with table creation
• Thinking it inserts or deletes data directly
• Mixing up functions with SQL commands like SELECT or DELETE

2. Which of the following is the correct basic syntax to create a function in PostgreSQL that returns an integer?

easy

A. CREATE FUNCTION myfunc() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION myfunc RETURNS int AS $$ RETURN 1; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION myfunc() RETURNS integer BEGIN RETURN 1; END LANGUAGE plpgsql;

D. CREATE FUNCTION myfunc() RETURNS int AS BEGIN RETURN 1; END LANGUAGE plpgsql;

Solution

1. Step 1: Check the correct syntax for function creation

   The syntax requires parentheses after the function name, the RETURNS clause, the function body enclosed in $$, and the LANGUAGE specified.

2. Step 2: Validate each option

   CREATE FUNCTION myfunc() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql; correctly uses parentheses, RETURNS int, AS $$ ... $$, and LANGUAGE plpgsql. Others miss parentheses, AS $$, or semicolons.

3. Final Answer:

   CREATE FUNCTION myfunc() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   Correct syntax includes parentheses, RETURNS, AS $$, LANGUAGE [OK]

Hint: Always use () after function name and AS $$ for body [OK]

Common Mistakes:

• Omitting parentheses after function name
• Missing AS $$ ... $$ around function body
• Not specifying LANGUAGE plpgsql
• Forgetting semicolons inside function body

3. Given the function below, what will be the output of SELECT add_one(5);?

CREATE FUNCTION add_one(x integer) RETURNS integer AS $$ BEGIN RETURN x + 1; END; $$ LANGUAGE plpgsql;

medium

A. 6

B. Syntax error

C. 5

D. NULL

Solution

1. Step 1: Understand the function logic

   The function takes an integer input x and returns x + 1.

2. Step 2: Apply the input value 5

   Calling add_one(5) returns 5 + 1 = 6.

3. Final Answer:

   6 -> Option A

4. Quick Check:

   Input 5 plus 1 equals 6 [OK]

Hint: Function adds 1 to input, so 5 becomes 6 [OK]

Common Mistakes:

• Confusing input and output values
• Expecting syntax error due to unfamiliarity
• Assuming function returns NULL without reason

4. Identify the error in the following function definition:

CREATE FUNCTION multiply_by_two(x integer) RETURNS integer AS $$ BEGIN RETURN x * 2 END; $$ LANGUAGE plpgsql;

medium

A. Missing RETURNS clause

B. Incorrect function name syntax

C. Missing semicolon after RETURN statement

D. LANGUAGE plpgsql is not allowed

Solution

1. Step 1: Review function body syntax

   In PL/pgSQL, each statement inside the function body must end with a semicolon.

2. Step 2: Locate missing semicolon

   The RETURN statement lacks a semicolon after x * 2, causing a syntax error.

3. Final Answer:

   Missing semicolon after RETURN statement -> Option C

4. Quick Check:

   Statements inside function need semicolons [OK]

Hint: Check for semicolons after each statement inside function [OK]

Common Mistakes:

• Forgetting semicolon after RETURN
• Misplacing LANGUAGE clause
• Omitting RETURNS clause
• Using invalid function names

5. You want to create a PostgreSQL function concat_names that takes two text parameters and returns their concatenation separated by a space. Which of the following is the correct function definition?

hard

A. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ RETURN a + ' ' + b; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a || ' ' || b; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN concat(a, ' ', b); END $$ LANGUAGE plpgsql;

D. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a & ' ' & b; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Understand string concatenation in PostgreSQL

   PostgreSQL uses the || operator to concatenate strings.

2. Step 2: Evaluate each option's concatenation method

   CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a || ' ' || b; END; $$ LANGUAGE plpgsql; uses || correctly with BEGIN...END and semicolons. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ RETURN a + ' ' + b; $$ LANGUAGE plpgsql; uses + which is invalid for text. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN concat(a, ' ', b); END $$ LANGUAGE plpgsql; misses semicolon after END. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a & ' ' & b; END; $$ LANGUAGE plpgsql; uses & which is invalid.

3. Final Answer:

   CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a || ' ' || b; END; $$ LANGUAGE plpgsql; -> Option B

4. Quick Check:

   Use || for text concatenation in PL/pgSQL [OK]

Hint: Use || operator for text concatenation in PostgreSQL functions [OK]

Common Mistakes:

• Using + or & instead of || for strings
• Forgetting semicolon after END
• Missing BEGIN...END block for multiple statements