Hi, I am trying to make and use a function that up...
# sqll
Lars
05/14/2022, 12:59 PMHi, I am trying to make and use a function that updates a jsonb field. However, I am having trouble using the variables inside the jsonb object.
Copy code
sql
CREATE OR REPLACE FUNCTION public.updateoffset(site text, offsetnumber integer, toscrape integer)
RETURNS void
LANGUAGE sql
AS $function$
update settings set "offset" = coalesce("offset", '{}') || '{"$2": {"toscrape":$3}}'
where site = $1;
$function$ Copy code
sql
SELECT * FROM public.updateoffset('test', 2, 2)b
burggraf
05/14/2022, 2:01 PMThis part looks wrong to me:
coalesce("offset", '{}')l
Lars
05/14/2022, 2:10 PMthe column is called offset. Very much inspired by this code: https://stackoverflow.com/questions/63854471/with-postgres-how-can-i-use-update-to-insert-a-new-jsonb-value-if-null-or-amen
Lars
05/14/2022, 2:12 PMThe code itself works perfectly creating an object in the offset column or updating it if it exsist. So the "offset" part is fixed. I wan't the $2 exchanged with the variable "offsetnumber" and $3 with "toscrape"
Lars
05/14/2022, 2:14 PMThe offset field will either be null or an object with one or more objects like this:
Copy code
{
"100": {
"toscrape": 103
}
}b
burggraf
05/14/2022, 2:18 PMAh, sorry, I missed that it's a column in your settings table? So are you getting an error message with this? Also, one thing I usually do is force the JSONB text to a JSONB type, i.e.
'{"$2": {"toscrape":$3}}''{"$2": {"toscrape":$3}}'::JSONBburggraf
05/14/2022, 2:19 PMI think in this case it's resolving to a string, and needs to be turned into JSONB. Not sure if typing it to JSONB like this will do that or if you'll need a function to convert text to JSONB here. Try the `::JSONB`first.
l
Lars
05/14/2022, 2:20 PMfunction itself works perfectly. I want the $2 and $3 changed to offsetnumber integer, toscrape integer
Lars
05/14/2022, 2:22 PMright now it errors because of the $3 - it expects either an int or a ' " '
Copy code
ERROR: invalid input syntax for type json
LINE 7: ...ttings set "offset" = coalesce("offset", '{}') || '{"$2": {"...
^
DETAIL: Token "$" is invalid.
CONTEXT: JSON data, line 1: {"$2": {"toscrape":$... 0.000 seconds. (Line 1).Lars
05/14/2022, 2:23 PMI am searching for some way of interpolating the variable (both $2 and $3).
Lars
05/14/2022, 2:24 PMonly $3 gives errors as it is not valid json
Lars
05/14/2022, 2:24 PMand $2 just inserts "$2" as string instead of the $2 value
b
burggraf
05/14/2022, 2:26 PMmaybe try :: to typecast those individual parts
burggraf
05/14/2022, 2:26 PM$3 might need to be "$3"
l
Lars
05/14/2022, 2:27 PM Copy code
sql
update settings
set "offset" =
coalesce("offset", '{}') || format('{"%s": {"toscrape":%s}}', $2, $3)::jsonb
where site = $1;Lars
05/14/2022, 2:27 PM^^This worked perfectly!
b
burggraf
05/14/2022, 2:28 PMinteresting
2 Views