CODEWITHSUNDEEP
sqlpostgresql
Sameer
Sameer

Reputation: 705

select all columns from both tables postgresql function

i have a simple sql join query

SELECT a.*,b.*
FROM Orders a
JOIN Customers b ON a.CustomerID=b.CustomerID

which selects all columns from both tables . I need to achieve the same in Postgresql function,but i am not able to select data from 2nd table

CREATE  FUNCTION get_data (p_pattern VARCHAR,p_year INT) 
 RETURNS TABLE (
orders.*,Customers.*
) 
AS $$

Upvotes: 4

Views: 8295

Answers (2)

Abelisto
Abelisto

Reputation: 15614

The one problem is that neither function nor views can return the columns with same names (in your example columns CustomerID presented in both tables). And the another one - syntax:

RETURNS TABLE ( column_name column_type [, ...] )

from the official doc, nothing about table_name.*.

Aside of the obvious solution where you specifying the complete list of columns, there is one trick with composite (row, record) types:

CREATE  FUNCTION get_data (p_pattern VARCHAR,p_year INT) 
  RETURNS TABLE (order orders, customer customers) 
AS $$

Note that you can use table/view names as types in declarations.

And in that case your query could looks like

SELECT a, b
FROM Orders a
JOIN Customers b ON a.CustomerID=b.CustomerID

After that the usage of the function would be:

select
  *, -- two composite columns
  (order).*, -- all columns from table orders
  (customer).*, -- all columns from table customers
  (order).CustomerID -- specific column from specific table  
from
  get_data(<parameters here>);

dbfiddle

Upvotes: 4

sumit chakraborty
sumit chakraborty

Reputation: 141

Considering the columns are present on which you are joining, you can do this:

    SELECT * FROM Orders a,Customers b WHERE a.CustomerID=b.CustomerID;

For more see the official docs: https://www.postgresql.org/docs/8.2/static/tutorial-join.html

You can also refer this: https://www.tutorialspoint.com/postgresql/postgresql_using_joins.htm .It has good examples and references what joins are there in postgre and how to do them.

Upvotes: 1

Related Questions