Questions tagged [functions]
A function performs some specified work, usually taking parameters as input. In terms of databases these may be specific to SQL or to the database vendor.
1
vote
0answers
3 views
PostgreSQL error: remote query result rowtype does not match the specified FROM clause rowtype, on remote function call
This is my remote function:
CREATE OR REPLACE FUNCTION public._test1()
RETURNS record
LANGUAGE plpgsql
AS $function$
DECLARE
rec record;
BEGIN
select 1,2 into rec;
return rec;
END $function$;
...
1
vote
0answers
42 views
How do I parse Address into Street Number, Street Name, Type, Unit Number?
How do I parse Address into Street Number, Name, Type, Unit Number? Is there a function which will conduct this?
2280 MEYERS AVE
ALDERGROVE AVE & S ...
0
votes
0answers
7 views
Pass 'interval' value to date_trunc function in PostgreSQL procedure
I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns.
The syntax in the ...
0
votes
1answer
23 views
What's the most appropriate type of index to use for this function-based query?
I'm using Postgres 9.5. I'm going to be running a lot of this kind of query
SELECT *
FROM mydb_article
where regexp_replace(url, '\?.*$', '') = :url_wo_query_string
so I figured I would need to ...
0
votes
1answer
48 views
Loop Block with execution doesn't persist
I need to iterate over a result set and update records from the data. I am using an anonymous block but the information does not persist.
Like this...
do $$
declare dados Record;
begin
for ...
0
votes
0answers
18 views
Returning a set datatype from MariaDB function
Given below is the syntax for the MariaDB's CREATE FUNCTION:
CREATE [OR REPLACE]
[DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
[AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([...
0
votes
0answers
13 views
PostgreSQL Function not returning message in Python 2.7 Script using EXCEPT NOTICE
I was not able to make the PostgreSQL function return the raised message in the python script it was being called from.
The scenario I wanted to make was:-
1) If the first table was created, a ...
0
votes
0answers
35 views
PostgreSQL Function: Returning Boolean Values depending on whether a table exists
This is my rookie attempt to create a function where it would return True or False depending on whether a table exists in the db. Unfortunately it outputs an empty table, and no raised messages;
...
0
votes
1answer
34 views
Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared
I recently installed an Oracle 12c in Oracle Linux 7.6, it was previously in Windows. In the first database restored, some functions are with compilation errors.
The query below informs you that ...
1
vote
0answers
16 views
Filtered Merge Statement [duplicate]
This is, probably, a really simple answer, but I am not coming up with it... Any help would be greatly appreciated:
I created a web form, to a stored procedure, that submits userID, deptID, and a ...
-1
votes
1answer
36 views
How to Set the Transaction Isolation level in a Table Value Multi Function
How do I set the transaction isolation level within in a table value multi function? I know it can be done in a stored procedure.
create function dbo.Test()
RETURNS @TestTable Table (test varchar(...
0
votes
1answer
29 views
How to trigger a function with DDL query?
I want to add a few sanity checks on my DDL statements. Unfortunately, I cannot run a validation separately due to some constraints. I am thinking to run validation inside the DB by calling a stored ...
0
votes
0answers
34 views
Postgres - Ignore result of function call in select statement
I would like a way to run a function on all values returned from a query where the return value of the function is unimportant (even void in this case). A select statement may be the wrong way to do ...
2
votes
1answer
65 views
How to find function definition
How can I find the definitions of below system functions in SQL Server:
has_access
permission_name
I tried with DAC connection but getting below error:
Msg 15009, Level 16, State 1, Procedure ...
0
votes
1answer
36 views
PostgreSQL functions inside where clause
I have table like this:
table a (
id
--
p1 text,
p2 text,
p3 text
)
When I write some function, which use this table, I need to write code like this
create or replace function ...
...
1
vote
1answer
113 views
How to pass an array to a plpgsql function with VARIADIC parameter
I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.
I have already defined the function
CREATE OR REPLACE FUNCTION mix_table_fields(...
4
votes
1answer
78 views
SQL Function, get value to send to CLR
I have a CLR to use the powershell to have access to the RichTextBox Object from .net.
On the SQL Function I have created like this:
CREATE FUNCTION [dbo].[FicheiroChines](@rtf [nvarchar](max), @...
2
votes
1answer
29 views
Postgres triggers for group_id
I have a table that looks like this:
CREATE TABLE transactions(
id SERIAL NOT NULL PRIMARY KEY,
value NUMERIC(10,3),
note TEXT,
group_id INTEGER);
I'd like to update the group ID automatically with ...
2
votes
3answers
61 views
Pass array of mixed type into stored FUNCTION
I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction.
the problem here is, that one argument is list of n ...
0
votes
2answers
64 views
Postgres list constraints that use a stored procedure
I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions.
something like:
CREATE TABLE payment (
amount bigint,
CONSTRAINT amount_gt_0 CHECK (...
0
votes
2answers
50 views
How to efficiently handle querying for different combinations of optional parameters
Consider the following Tables:
Table: Parts
part_id INT PK
part_number VARCHAR
Table: Jobs
job_id INT PK
job_number VARCHAR
Table: Job_Parts
part_id INT FK PK
job_id INT FK ...
0
votes
1answer
61 views
SQLServer Row level security - Enforce from within database
Have a multi-tenant app running on a single sql 2016 db, I have a RLS (row level security ) predicate on customer_id (which is unique for each tenant) and the column exists in all tables.
CREATE ...
1
vote
0answers
9 views
Querying a synonym (for a table using linked server) gives no data in return
I have a function which run in an application at a server. This server have a ODBC connection (user DSN) to the SQL server which is set to use windows authentication.
The function is supposed to ...
0
votes
1answer
52 views
Custom return type of a function in Oracle PL SQl
I have a query and I want to wrap it into a function. Currently query returns in_count, in_sum, out_count, out_sum. I only know how to return a "primitive type". So currently I have four different ...
1
vote
1answer
111 views
How to use variables in procedure (not function)? [closed]
As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to:
declare a variable
select a value into a variable
insert the variable into ...
1
vote
1answer
30 views
Return table with separate columns from function
I have this function, it's simple enough:
CREATE OR REPLACE FUNCTION test_function()
RETURNS TABLE(
id INTEGER,
organization_id INTEGER,
email_address CHARACTER VARYING
)
AS $...
5
votes
1answer
102 views
PostgreSQL UDF (User Defined Functions) overhead
Disclaimer
The task may seem esoteric, but nevertheless I want to create a POC of some sorts.
The goal
My goal is to make PostgreSQL database (version 10) expose an API to an application that uses ...
0
votes
1answer
46 views
Realtime Update MySQL on every Update occurred on PostgreSQL
I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they ...
1
vote
1answer
251 views
What is included in ALL permissions for functions in PostgreSQL?
I am trying to find references to what the difference between EXECUTE and ALL is in:
GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION | ALL FUNCTIONS IN SCHEMA }...
but all I can find is what ...
1
vote
2answers
57 views
Functions causing Serial plan.What to use instead? [closed]
I have few stored procedures in my SQL Server database.I see that these stored procedures are running in serial mode from their execution plan.
For example :
CREATE PROCEDURE [dbo].[DemoProc]
@...
3
votes
1answer
58 views
What is the difference between a SQL function doing a SELECT versus a PLPGSQL function doing a RETURN QUERY SELECT?
Is there a difference between a plain SQL function doing a SQL query:
create function get_sports() returns setof sport as
$body$
select * from sport;
$body$
language sql stable;
and PLPGSQL ...
4
votes
1answer
84 views
TSQL - create a function that takes a multi-row query result as an argument
I work with a variety of servers on mostly SQL Server 2014 and earlier.
I have boiler-plate code that can take the results of an arbitrary select statement (returning a list of varchar or the like) ...
0
votes
1answer
121 views
Equivalent of DB2 function “decimal(numeric expression, precision, scale)” in PostgreSQL
Is there an equivalent of the DB2 function decimal(numeric expression, precision, scale) in PostgreSQL? Or how would I do this functionality in PostgreSQL?
The DECIMAL function returns a decimal ...
1
vote
0answers
23 views
PostgreSQL- user-defined types: note sure what internallength/alignment is for linked list type
I'm currently trying to implement at user-defined data type in Postgres in the form of a linked list with each node holding an int and a pointer to the next node, here are my input functions:
// ...
3
votes
0answers
43 views
Need a Function that allows parameters to pass as argument for logic inside the function in Greenplum
Suppose I have a query :-
select 'public.test123 is' || ' ' || quote_literal(obj_description('public.test123'::regclass)) || ';' as test;
test
-------------------------------------
...
2
votes
3answers
135 views
Why does using Format vs Right to apply padding cause estimated number of rows to dramatically change?
I was working on a query at work, that had a left join like
cast(cola as varchar) + '-' + right('000' + cast(colb as varchar), 3) = x
The actual execution plan for this query was fairly close, est ...
2
votes
1answer
111 views
Efficiency of Scalar UDF vs TVF
I am trying to optimize the rollup code for my company and ran into a very peculiar issue. I converted many Scalar functions to be TVFs and they all seem to run more quickly than the original, which ...
1
vote
1answer
175 views
How to create `instead of` trigger on a table in postgres?
I want to create a trigger for a table in postgresql. My table contains data about events and includes room number, start time and event duration. Upon new insertion to the table I want to check if ...
3
votes
1answer
93 views
How can I find out if a SQL Function can be inlined?
In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will ...
8
votes
1answer
417 views
Can declaring function volatility IMMUTABLE harm performance?
Postgres functions are declared with volatility classification VOLATILE, STABLE or IMMUTABLE. The project is known to be very strict with these labels for built-in functions. And with good reason. ...
1
vote
1answer
101 views
MySQL / PostgreSQL: how to call a script when a row inserted / updated
Let's assume, I have two DBs, either MySQL or PostgreSQL (sorry, but we have not yet decided what DBMS to use).
Every second multiple rows are being inserted or updated on the first DB. For each ...
2
votes
1answer
89 views
Function in JOIN condition
I have big dynamically constructed query with
FROM main_table t
LEFT JOIN table1 t1 ON my_func(t.col1, 1) = t1.col1
...
LEFT JOIN table6 t6 ON my_func(t.col1, 6) = t6.col1
Query is very slow, but if ...
-2
votes
1answer
37 views
What is TRANSFORM FOR TYPE?
PostgreSQL 11 introduces TRANSFORM FOR TYPE, from these docs by HP, PostgreSQL 11 New_Features
postgres=# CREATE EXTENSION jsonb_plperl CASCADE ;
NOTICE: installing required extension "plperl"
CREATE ...
1
vote
1answer
64 views
Logic of Assigning values to variables in Scalar-valued Functions
I am currently working on updating old scalar valued functions with the hope of making them more efficient or changing them into Inline TVFs.
While troubleshooting my rewrites I came across some ...
8
votes
1answer
844 views
Is it Possible to Overload SQL Server Functions?
Is it possible to overload a sql server function? Either a scalar, like ltrim, or an aggregate function, like count?
Even if this was a really, really, bad idea. Is it possible?
Somewhat of a ...
3
votes
1answer
284 views
How could a “SECURITY DEFINER” function in PG be insecure with an improper search_path?
The PG docs provides this example of a properly written security definer function with a search_path:
CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed ...
0
votes
0answers
23 views
Task oriented VS general function definition
Which approach is better to use for an application with more than 1 thousand db functions:
1- Defining Global functions and filtering data in server side and using data
( It causes some performance ...
0
votes
2answers
142 views
How to get around not being able to use Temp Tables in Functions?
So, to get straight to the point. A Client wants to be able to view all of their staff of a certain position are going to be available to perform a set of shifts between 2 dates.
Seems simple, have a ...
3
votes
1answer
134 views
Is there any performance advantage to marking a SQL function as LEAKPROOF?
PostgreSQL has an attribute LEAKRPOOF which you can use to declare a function,
LEAKPROOF indicates that the function has no side effects. It reveals no information about its arguments other than by ...
1
vote
2answers
798 views
Postgres - Return default value if regex match fails
I'd like to attempt a regex match, and return null if it fails.
The following query attempts to find the first number in a string. The result ignores the entry with text 'blah'. I'd prefer it to ...
