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 $$
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>);
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.
inner join is just an 'alternative' and 'less used'. :S Nowdays, the reality is opposite.
select *selects from all tables in the query in every DBMS I have ever used. Does PostGreSQL differ here? That seems weird/unlikely to me. And yes, "I am not able to X" is not a valid problem description.selectstatement from the desired tables/columns, not just the table names. Also, presumably you really intend for the function arguments to be used in thewhereclause, as otherwise the function would not achieve anything useful.select * from tblA inner join tblB on blahand get all the columns just fine. You then need to incorporate that select into your function in a syntactically correct way, not what you've tried above.