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.
-4
votes
0answers
24 views
Strange call function error [JDBC + POSTGRESQL] [closed]
i have a jdbc client that call a stored procedure on database, but i occours a strange error, and log of pgadmin is not so clear to describe what happen.
This is how the function looks like:
CREATE ...
1
vote
0answers
77 views
Inline TVF with One Row Result
I have several times converted scalar functions into inline tvf. Usually this is the basic design I used in inline tvf:
CREATE FUNCTION TVF(@ID INT)
RETURNS TABLE
AS
RETURN(
WITH BASE AS(
...
1
vote
2answers
92 views
Reducing size of image returned by view
We are storing a list of images in a table in an image column. These are stored as their raw image size so they can be viewed in their details at full resolution.
The problem I have is I want to show ...
1
vote
1answer
39 views
How to call an Oracle Function with multi out Parameters though LINKSERVER
I need help as I am facing this problem. when I pass 2 output parameter to a function through a linked server I am getting this error. For 1 out parameter its working fine but when I use two output ...
3
votes
1answer
95 views
Running functions in parallel
This is for SQL Server 2012.
We have some import processes for FTP files that are picked up and read into a staging table, from there we massage/check the data before moving into production. One of ...
1
vote
1answer
84 views
Postgresql function to create table
I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this:
CREATE OR ...
-1
votes
1answer
33 views
checking a folder whether required file is present at a regular interval [closed]
i have 4 folder hierarchy in a following format :
Hourly Daily Weekly Monthly.
In every folder i have files which is coming in a frequency based on their folder's name.
Now , i have ...
1
vote
2answers
189 views
Can SQL Server 2008 / 2012 have execute permission in a function for a user, but not for a table used in the function?
I have a function that returns a table. This function SELECTS DB1.dbo.table1
user1 has SELECT permission in this function only, but when selecting from the function error comes up stating that the ...
1
vote
1answer
95 views
Pass a table array as a parameter to an upsert function in postgresql
i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
1
vote
0answers
126 views
Calling a function in remote database inside a stored procedure
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I ...
3
votes
1answer
112 views
How does this undocumented syntax work? {fn CurDate()} or {fn Now()} etc
Recently I've been looking through some fairly old stored procedures that were written for SQL Server 2005, and I've noticed something that I don't understand. It appears to be some type of function ...
0
votes
1answer
18 views
Cannot create perlplu function
Running PostgreSQL 9.1.8 on Xubuntu 12.04, installed from the repos.
From the shell, I have executed:
createlang plperl db_name;
createlang plperlu db_name;
As the superuser running psql, I have ...
2
votes
1answer
78 views
How to access the number of rows accumulated by RETURNs in PL/pgSQL
When performing
RETURN QUERY ...
in a PL/pgSQL function, is it possible afterwards to directly access the number of rows accumulated into the pile of records, which are returned when the function ...
1
vote
2answers
187 views
Recursive query using plpgsql
I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure.
I have a data table and a table to link the data together:
DATATABLE
-----------
id ...
5
votes
1answer
2k views
Create index if it does not exist
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?
This is a similar issue ...
5
votes
3answers
207 views
How to pass a table type with an array field to a function in postgresql
i have a table called book
CREATE TABLE book
(
id smallint NOT NULL DEFAULT 0,
bname text,
btype text,
bprices numeric(11,2)[],
CONSTRAINT key PRIMARY KEY (id )
)
and a ...
-1
votes
1answer
106 views
Undefined reference to my function [closed]
I'm working on a project on postgresql 8.4 server side. I'm adding some functionality in order to store some query informations.
I need to call two functions (start_create_profile() and check()) ...
0
votes
1answer
91 views
Parameter triggers different execution plan, but why?
I have a table
CREATE TABLE [dbo].[Numbers]
(
[Date] [date] NULL,
[Time] [time](3) NULL,
[Value] [char](10) NULL
)
and the table has > 10 Billion rows, therefore it ...
7
votes
3answers
244 views
Store a formula in a table and use the formula in a function
I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
1
vote
1answer
1k views
Invalid use of side-effecting operator 'INSERT EXEC' within a function [closed]
I'm trying to create a scalar function that will return a delimited list from some sql that would be passed in. Unfortuantly where I work the previous people responsible for the database thought it ...
4
votes
1answer
521 views
Oracle extract slow function call from a WHERE clause
I have an oracle table in which dates are stored as the number of minutes since a specific date. It looks something like this:
CREATE TABLE MY_TABLE
(
SOMETHING NUMBER(15,1) NOT NULL,
...
0
votes
2answers
86 views
looping through a table type variable
I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value.
CREATE OR REPLACE FUNCTION ...
0
votes
2answers
210 views
Interactive INSERT / UPDATE function to implement UPSERT
I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 ...
1
vote
1answer
136 views
PostgreSQL How do I convert struct text to plain *char in C functions?
I'm using my internal C function which doesn't know about postgresql's text struct, how do I pass text argument when char * expected?
#include <stdio.h>
#include <string.h>
#include ...
0
votes
2answers
91 views
SQL Server 2008 - How did I solve the need to check the column definitions quickly?
Another tool I developed for myself, with regards to making the creation of various DML operations easier in a large database of hundreds of tables, is a nifty user defined function to easily show ...
1
vote
3answers
507 views
Support both stored procedures and functions with same TSQL code base?
We have a need for both Microsoft SQL Server 2008 functions (ITVFs) and stored procedures to provide the same interface and functionality to end-user reporting frontends.
Given the following ...
3
votes
2answers
144 views
Custom Postgres function not working
I wrote this function that is supposed to iterate through a text array and convert each element (which is a typecasted integer) to hex, append to a text variable, then return the hex. However, I get ...
5
votes
2answers
360 views
SQL Server SMO and PowerShell formatting
I have probably been looking at this for too long to figure this out...
What is the easiest way I could get this exported to a CSV format?
Would it be better to dump this to a table in a database ...
3
votes
1answer
129 views
How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans?
Server A and Server B have identical hardware and instance configurations (A is Production, B is QA). B's DBs were restored from A's backups from one week ago. I was provided this query by the ...
1
vote
1answer
246 views
How can I restore the sql_variant_property of baseType back to a table variable in SQL Server
Ok, I know the title is confusing but here it goes. BTW I am using SQL Server 2008
I have a table that I am trying to use as a storage container. It has 3 columns:
reportID integer,
dataLabel ...
3
votes
2answers
252 views
How to concatenate a column after x rows?
I have a table with columns
object varchar(255),
frequency tinyint(3) unsigned,
I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as
.. ...
7
votes
1answer
144 views
Estimated versus actual query plan with function calls
I have this query on SQL server, a merge replication query:
SELECT DISTINCT
b.tablenick,
b.rowguid,
c.generation,
sys.fn_MSgeneration_downloadonly
(
c.generation,
...
3
votes
1answer
154 views
A function to check if a column allows NULL
Is there a way to write an insert/update query that checks if a column allows NULLs: If it does set the column to NULL, and '' (empty string) otherwise?
I would be something like:
UPDATE mytable
...
2
votes
1answer
143 views
Create Enumerated Function Parameter Type
I want to create a function similar to DATEDIFF in MS SQL Server 2008 R2. The first parameter in DATEDIFF is a datepart. Can I use datepart as a parameter in my function? If not, how do I create a ...
1
vote
1answer
219 views
Is a query with UNIX_TIMESTAMP using indexes in mysql?
If I use
.. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW()
in a SELECT, does this query still make use on an index, set on some_timestamp ?
0
votes
1answer
402 views
MySQL - Create a function from another function
I want to create a function same like my old functions.
When I create a table, I can use `AS to other table as follows:
CREATE TABLE dummy2 AS SELECT * FROM dummy1;
Can I do the same thing with ...
5
votes
2answers
317 views
T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs
I am trying to write a function which will split a varbinary(max) field into a table with 16 byte GUIDs, but am struggling. I haven't been able to find any examples or walkthroughs on how to do this ...
0
votes
1answer
362 views
Find ID columns with null value in oracle database
I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database
Any suggestion?
1
vote
1answer
302 views
Transform all columns records to lowercase
I'm using PostgreSQL 9.1 and I have a users table with a login column.
login names are case-sensitive, for example Bob, MikE, john. I would like to transform all these records into lowercase. ...
4
votes
1answer
223 views
Is it possible to wrap aggregate functions in Postgres?
Postgres' string_agg(expr, delimiter) function is great. But I would like to have a version that takes a single argument -- the field to aggregate -- and assumes a delimiter of ', ' since that is ...
4
votes
1answer
2k views
How to use aes-encryption in PostgreSQL?
I tried aes-encryption by using following statement:
SELECT encrypt('test', 'key', 'aes');
which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
2
votes
2answers
585 views
User Defined Function Return value
Hello all I have a function:
ALTER FUNCTION [dbo].[getContentURL]
(
@VID int,
@accountID int
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @cTable varchar(50) = ...
0
votes
0answers
59 views
How to perform several operations at DB level
I need to insert 25 000 or 50 000 test rows for make some performance tests in several tables in my DB. I'm using PHP but the script takes to long to run so in shared hosting is a pain! I'm asking if ...
3
votes
3answers
294 views
Performance of Inline-TVF vs. Views
I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together ...
0
votes
2answers
359 views
User defined function performance disadvantages
I have a database with a lot of UDFs that are called by a long running process involving lots of data manipulation and calculations.
My thinking in using UDFs is to separate out logical units of ...
2
votes
1answer
251 views
Error on Oracle calling external procedures
I have a C library which should be called from a pl/sql function so I make a .so library and after that I create a pl/sql function to call it.
the C source(libcprog.c) is like this:
int exec_hmmftg ...
3
votes
1answer
924 views
Function/procedure to use dblink to fetch remote data and insert to multiple local tables
Am trying to fetch data from remote db and insert into three local tables. Currently I have a query which is successfully getting the data into one table.
But now I was thinking of creating a ...
2
votes
3answers
431 views
How can you save all user-defined functions to txt files on your local hard drive?
I am running SQL Server 2008 and need to back up the UDF scripts/queries that I have created. How can this be done in a timely manner? I tried right clicking on the folder icon where the UDF are saved ...
5
votes
2answers
208 views
Create function in central database or repeat in each database?
One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database ...
2
votes
1answer
279 views
DB2 equivalent to T-SQL's REPLICATE?
In T-SQL there is a method, REPLICATE(char,N), which will make a string with N values of a certain character. This is very helpful when querying into JDE.
Does anyone know if there is a DB2 ...
