I am working on a multitenant application. Most of the tables contain a column tenant_id. I want the number of rows in all tables for a particular tenant_id. Example, Tables:- Sample result to get number of rows in every table having tenantid = 64 TableName Count Employee 2 Student 3 Teacher 1 How do I loop t…
Tag: sql-function
Why does Postgresql function not work as expected
I have table like this I create get function like this I tried to call function like this but return json all 3 row instead of 1 row with booking_id 1467 can you explain root cause ? Answer The root cause of you problem is naming your parameter (booking_id) the same as a column name (booking_id) in your table…
How to insert result of select query (with default if return no rows) as part of Postgres Function?
For my Nextjs website, I am configuring the backend to automatically add user information to a public.profiles table whenever a row is inserted into the auth.users table (automatically done through Supabase’s authentication). I have a public.college_emails table that contains the names of over 1700 coll…
Avoid ‘out of shared memory error’ in PostgreSQL function
I have two tables, a table called companies_display with information about publicly traded companies such as ticker symbol, market cap etc. and a partitioned table stock_prices with the historical stock prices for each company. I want to calculate the beta of each stock and write that into companies_display. …
Passing parameters for a constraint function
I have the following table that joins driver and truck tables in order to assign trucks to drivers. I need to constraint entries that belong to the same driver on the same day, and DO NOT include the same truck code. It was suggested to create a boolean function to pass along dateval, truckval and driverval, …
Function to convert char(255) non standard dates into timestamps
I have database with a column of date strings that are in the format YYYY-MM-DDThh:mm:ss.3nZ I would like to transform them into TIMESTAMP so that I can then apply the BETWEEN function to identify rows that fall in a given time window (this being the ultimate goal of the exercise). I can’t just change t…
How to pass a user defined table type returned from a function as a parameter to another inline function (without ‘DECLARE’)?
I’m afraid the answer to this is “NO, you can’t”, but since I can’t find this explained anywhere I’m gonna ask anyway. Given a user defined table type: And a function that returns that type: And another function that accepts that type: Since the return value of fnFind fits …
Function returns row type as single value instead of one value per field
The only time I encountered a similar problem on SO is on here, but the proposed solution was not relevant for me. I call a PostgreSQL function, but it’s returning a single value instead of one value per field, which I can access. I have a table “Words”, which consists of a primary key word …
Select a column from table return by function in Oracle
I have a function that returns a table of custom objects. I wish to select a certain column by name from the returned result. The table of objects The function When I call the function this way, I get a result with a single column called SERIAL_NUMBER However, I can’t do something like this Is there a w…
I can’t get rows have NULL on all rows for column zfeaturekey based on zplid and code type?
I work with SQL Server 2012. I have an issue I can’t get rows from table #gen when it has Null only on all rows on zfeaturekey field based on zplid and codetypeid. I need to get rows that have NULL only on all rows ON zfeaturekey, but must be same codetypeid and same zplid . Output: This is the